MySQL Forums
Forum List  »  Microsoft SQL Server

Move Data from MySQL to SQL Server via SSIS
Posted by: Andrew Venuto
Date: April 29, 2013 05:47PM

How to connect and load data from MySQL to SQL Server

1 - Download the 32 bit ODBC driver.

go to the MySQL website and download: “mysql-connector-odbc-5.2.4-ansi-win32.msi”
NOTE: Do not use the 64 bit driver on BIDS 2008. BIDS 2008 is 32 bit. You will get a mismatch error when creating SSIS’s connection manager: “The specified DSN contains an architecture mismatch between the Driver and Application”

2 - Create a User DSN

You need to open the using windows 32 ODBC admin tool. DO NOT open the regular ODBC admin, in control panel. Open the ODBC admin located here: c:\Windows\SysWOW64\odbcad32.exe. If you use the default ODBC admin…it will not work. Additionally you must create a “User DSN” - NOT a System DSN. Otherwise it will not show up in SSIS
NOTE: the screens look the same so you will have no way of knowing whether you are in 32 BIT ODBC Admin tool or not.

3 – Create a new SSIS package and create an ADO.NET connection manager. Create your ADO.NET destination connection and object.(where you want to send the data)

4 – Change the Source ADO.NET properties.

You will get validation errors and your package will not run. You need to change the “ValidateExternalMetadata” to FALSE (in the “Advanced Editor” dialog box) of the ADO.NET source It will also give you metadata error…that’s ok…just click ok. It will still pull the metadata (column names/data types). You cannot select the tables as you would in SQL server. You need to type the SQL select statement.

5 - Run the package and should run and load normally.

Options: ReplyQuote

Written By
Move Data from MySQL to SQL Server via SSIS
April 29, 2013 05:47PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.