SSIS data Import from MySql

I am working with SSIS and trying to import data from MySql to SQL Server. I am ha开发者_开发问答ving issues trying to convert mySql datetimes to SQL Server Datetimes. Any suggestions?

Can you provide an example of the error or problem? I found some values in MySQL were out of range to SQL Server. In these instances, I used a NULLIF on the MySQL side to remove the bad values first. Rafal Ziolkowski's solution of converting to strings could work as well, but I personally would prefer to push the conversion off to the MySQL query if it is only impacting data columns.

Try convert date to string and then to datetime again.

What is the issue? Which SSIS data type are you using? DT-DBTIMESTAMP should work for both.

this took me few days to figure out...so I thought I would share my notes

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:

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.