asked
SQL Server 2008R2 to My SQL using SSIS
Hello everyone, I am trying to build a package in SSIS 2008(and also 2012 Data tools) to move a table(only few set of columns) from SQL Server 2008R2 to My SQL 5.x version. I tried this MySQL ODBC Drivers 5.1(from ODBC connectors) and also using ODBC Connector/Net 6.x version too. I am encountering the following the error: [ADO NET Destination [2]] Error: An exception has occurred during data insertion, the message returned from the provider is: 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 '"BusinessEntityID", "PersonType", "Title", "FirstName", "MiddleName", "LastName"' at line 1 [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "ADO NET Destination" (2) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (9). 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. I am able to transfer the data from MySQL to SQL Server 2008R2 and 2012 with out any difficulty, but not the other way around. Its an emergency situation at my new project. Thanks a lot for your help. Ram.
I have not worked in MySQL in some time. Based on what I remember there are a few slight differences in how the syntax is written. I think there is a data type mismatch as to why it won't insert into the table. Or possibly data length is too long and the column not long enough. Have you checked the reference manuel for the version of MySQL? Compared the data types? Column lengths?
Thank you for your response. I considered that into account and tried it with only 1 integer column. I also tried by converting data into UTF8 format as well. But still encountering the same error. Even this same error is occurring even I tried to load the data from a Flat file into MySQL using SSIS Data flow task. If someone is doing a similar kind of work, it would be great if you can post few steps that you took to counter this situation. Thanks.
15 People are following this question.
