Error while executing SSIS package to data migration from SQL 2012 server to MySql

1
votes

I'm trying to migrate data from MS Sql server 2012 to MySql server (5.1) using SSIS packages

Here is my data flow task which retrieves data using query from SQL Server and pumps to Mysql (one of its columns contains blob type)

enter image description hereenter image description here

and properties

enter image description here

Tried with ODBC connection manager and ADO.net(odbc driver) manager for destination.

The following is the error I'm facing, it is failing after inserting some records into MySql which I'm not able figure out the reason/ solution:

Error: 0xC020844B at DFT Finding Attachment, ADO NET Destination 2: An exception has occurred during data insertion, the message returned from the provider is: The connection has been disabled.

Error: 0xC0047022 at DFT Finding Attachment, SSIS.Pipeline: 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.

2
mysqlsql-serverssisblob

2 Answers

1
votes

The issue is with MySql max_allowed_packet, it set to 1MB so it's failing while iserting large blobs, so the answer is icreate it to 64MB

set global max_allowed_packet=64M

0
votes

I'd suggest to add a Data Conversion task into the Data Flow, between Source and Destination and make sure all the data types are converted properly, before inserting into the MySQL table.

I often experienced that SSIS cannot convert the datatypes by default.