Using SSIS 2008 to load data from MSSQLServer to MySQL
Posted on 2011-05-12
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 : An exception has occurred during data insertion, the message returned from the provider is: ERROR  [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 ?