Using SSIS 2008 to load data from MSSQLServer to MySQL
Hi,
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-l
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
SET SQL_MODE='STRICT_TRANS_TAB
However this doesn't seem to make much difference either.
Any ideas ?
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-l
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
SET SQL_MODE='STRICT_TRANS_TAB
However this doesn't seem to make much difference either.
Any ideas ?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Now I've got some other problems but I'll see if I can get through those.
Thanks once again.
ASKER
The updated ODBC connector (i.e. the solution) certainly helped advance the SSIS package. Now onto the other problems.
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?