I'm trying to import data from mySQL database to SQL server 2005. I installed mySQL odbc driver (3.51) and configured system DSN. However when I start SQL server import/export wizard mysql is not an option in data source dropdown.
What do I miss?
I never worked with MySQL, but there is a general approach. If you were able to create DSN, try to create a DTS package on SQL Server. I do not use SQL Server2005, but 2000. There is Other Connections icon under Connections in DTS designer. Configure this connection for MySql. The rest is yours - just configure the destination for your MySQL data.
I agree with dbmjs that creating a DTS package will probably be an easy way of transfering the data. Just a couple of quick notes:
1) DTS in sql server 2000 works great for smaller < 1mil records. Anything bigger than that it seems to run into some issues. (You can always make a couple of smaller packets and import them)
2) The DTS equivelant in SQL Server 2005 is named SQL Server Integration Services (SSIS), and works great no matter the size or amount of records.