Using SSIS 2008 to load data from MSSQLServer to MySQL

I'm trying to moving data from MSSQlserver 20008 to MySQL 5.0.81 using SSIS 2008. The package is meant to be something which is re-run on a regular basis (probably daily or weekly). As part of the package is a stored proc which produces rows of a table which is then written to the MySQL table. Essentially the error I'm getting is:

Error: 0xC020844B at Get New Time Periods to load into FluID, ADO NET Destination [61]: An exception has occurred during data insertion, the message returned from the provider is: ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.28-rc-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"TIME_PERIODS" ("PERIODICITY_TYPE_ID", "TIME_PERIOD_NAME", "YEAR", "MONTH", "WEE' at line 1
Error: 0xC0047022 at Get New Time Periods to load into FluID, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "ADO NET Destination" (61) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (64). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

The connection to the MySQL db is using .Net/ODBC Data provider and I've also tried .Net/MySQL provider too, pretty much with the same error. The properties for both these connections use the defaults although I've also changed the RetainSameConnection property to True when looking at some of the other "similar" issues in the knowledge base. One is particular was to prefix the insert with an Execute SQL Task which runs

However this doesn't seem to make much difference either.

Any ideas ?
Who is Participating?
Reza RadConsultant, TrainerCommented:
seems that your connection is ODBC, am I right?
if yes , try with .NET provider , download it from here:

also this is good reference to how to connect to mysql from SSIS:

there are two data providers for mysql, ODBC and .NET, try each of them,sometimes just one of these methods works.
Jason Yousef, MSSr. BI  DeveloperCommented:

I don't think the error is regarding the connection..Also you can check That link

Does the SP works fine from inside SSMS?  I think it's a query problem, can you post your quesry and Mysql Version?
JohnRawloAuthor Commented:
Hi there,
The link you mentioned was one of the ones I used to try either the Net/ODBC Data provider or the .Net/MySQL provider = they both gave the same result.
As for the MSSQL stored procedure that's pretty straight forward - works fine within and outside SSIS. I've even tried from a table instead of an sp with the same result.
The actual MSSQL server version is 2000 (although SSIS 2008 is being used) and the MySQL server version is 5.0.81.
JohnRawloAuthor Commented:
Thanks for that. The later version of the ODBC connector seemed to fix things.
Now I've got some other problems but I'll see if I can get through those.

Thanks once again.
JohnRawloAuthor Commented:
The updated ODBC connector (i.e. the solution) certainly helped advance the SSIS package. Now onto the other problems.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.