From MySQL to MSSQL 2005

From MySQL to MSSQL 2005

• zut

  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

  
  

• vinson.zeng

  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
  
  

• Richa

  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.

  
  

• Felix Planjer

  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
  

• Deicide

  I have the same problem.  Anyone know
  

• blewisjr

  I've MySql ODBC driver 3.51 installed on the same box as SSIS and have created ODBC data source. What now
  

• Volcom98

  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.

  
  

• mace151

  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

  
  

• Andy Tarpey

  Hi,
  just wondering, did anyone manage to get it to work properly
  
  Daren
  
  

• Franz Strele

  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 ;-)

  
  
  

• aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

  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

  
  

• Robert Varga

  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.

  
  

• C26000

  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.

  
  

• NoEgo

  I ended up using Select Into statements using Linked Servers and Openquery. I ran out of time to find a better solution.
  
• From MySQL to MSSQL 2005