From MySQL to MSSQL 2005

Hello! I'm installing the MSSQL 2005.
And i want to transfrom the DB from my old MySQL DataBase to new MSSQL Database. How can I do this? With Import|Export Utils I can't do this task =(

Pleasure Help me.

ThanksI have the same problem. Anyone know?|||First of all do you have MySQL ODBC driver installed on the same box you have SSIS? If you do - did you create ODBC data source?|||

In my case, the MySQL ODBC driver is installed on the server, and I'm able to create a linked server to the database. The problem is that the only mechinism for ODBC in the Import/Export Wizard is the .NET ODBC provider. When using this provider, the option to "Copy all tables" is greyed out because apparently it can't read the metadata.

Given that i have hundreds of tables to move, it is not helpful.

|||

Eyfel, I think the response I got from my similar question about PostreSQL will answer this one as well.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193071&SiteID=1

|||I've MySql ODBC driver 3.51 installed on the same box as SSIS and have created ODBC data source. What now?|||Is this a one time deal? If so, why not use mysqldump to create

your scripts to create tables. I believe it even has options to

make the DDL consistent with MSSQL.

Once you have the tables created, you should easily be able to import the data.

Larry Pope|||

That is a good idea, but it still leaves a big problem. After you have the structure built, you still have to create a separate data reader with a sql statement for every table in the database. When you have hundreds of tables, it presents a time challenge. And on top of that, DTS can handle implicit data conversions (like boolean to bit), and SSIS requires explicit conversions for each of these data type changes.

That's why the SQL 2000 DTS Import is still superior, where you can pull the metadata to create the tables and the data transfer in one simple wizard driven setup. Five minutes versus several days or weeks. I just hope they resolve it.

|||

to make a connection I did the following, install Myodbc driver 3.51 from MySQL site Setup an DSN (system or user) from the ODBC options in Admin tools.

Use the import/Export wizard found on right click all tasks on an database in mssql 2005.

Set it as a .net framework provider for ODBC

Enter the following in the connection string

Dsn=DSNNAME;Driver={MYODBC3.dll};database=DATABASE;option=0;pwd=PASSWORD;port=0;

server=SERVER;uid=USERNAME

replace DSNNAME,DATABASE,PASSWORD and USERNAME with your information.

Works a treat.

Alan ;-)

|||

Alan, do you get the option to "Copy all Tables" when you do this? I'm not getting it when I do the same thing. I can use the wizard, but I'm forced to type a sql statement which limits me to one table per import. I need the "Copy all Tables" options so I can copy hundreds of tables fairly easily.

From what I can tell, the OLE ODBC used in SQL 2000 allows me to do this, but the .NET ODBC does not. Let me know if I'm missing something. Otherwise I'm stuck using SQL 2000's DTS indefinitely.

Thanks.

|||

I managed to get the "copy all tables" option enabled in the SSIS import wizard if I use the mysql.oledb connector (from Feb 2001) instead of the ODBC driver. Reads the meta-data fine (so far ...).

Caveat: I'm connecting to a MySql 4.0 DB and still in the experimental stage. Has anybody else gone down this path?

Bill

|||

Issues I've noticed so far with OLEDB:

a) mysql 4.0 date column type is not recognized (requires using "Edit" to change to change dest type from <blank> to Datetime).

b) mysql 4.0 tinyint column type is not recognized (as above, use "Edit" to change type from <blank> to tinyint).

c) meta-data names are displayed with a trailing [] (The square "I don't know what else to print" character) in the wizard. This occurs both in the source table and column names, and of course in the new table and column names (not sure what overall impact this has).

b

|||

Thanks for posting Bill. One question, how do you get the MySQL OLEODBC driver to show up in your driver list? I have it installed but it does not show up as an available driver.

I appreciate any help you can lend. In SQL 2000 the driver just shows up in the list so I'm not sure what I'm missing.

|||Hi,

just wondering, did anyone manage to get it to work properly?

Daren|||I ended up using Select Into statements using Linked Servers and Openquery. I ran out of time to find a better solution.