Failed to pass SQL Server varchar (max) to MySQL text using SSIS
I'm trying to transfer a table from SQL Server to MySQL using SSIS and it doesn't work on one column, which is varchar (max) on SQL Server and textual in MySQL. So far I have tried to do this as OLE DB for ODBC data stream and BCP as flat file for ODBC data stream. The field in question is configured in SSIS as DT_TEXT. In both cases, it produces the following set of errors:
[ODBC Destination [47]] Error: Open Database Connectivity (ODBC) error occurred.
SQLExecute returned error while inserting row 1
[ODBC Destination [47]] Error: Open Database Connectivity (ODBC) error occurred.
state: 'HYC00'. Native Error Code: 0. [MySQL][ODBC 5.2(a) Driver][mysqld-5.1.69-log]
Parameter arrays with data at execution are not supported
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput
method on component "ODBC Destination" (47) failed with error code 0x80004005 while
processing input "ODBC Destination Input" (58). 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.
When I choose to ignore the column, both versions of the package work fine.
I would also like to point out that this was previously done using the linked server with openquery and it worked without issue. However, due to performance issues, we are trying to move away from linked servers.
UPDATE: I was able to accomplish the desired behavior using SQL Server BCP out
followed by MySQL LOAD DATA INFILE
, but it would be nice to know how to do it using SSIS data stream as it is definitely an easier way to make it need to be faster as it works in memory.
source to share
Make sure the text encoding in the source and destination address matches what you are converting to SSIS, if not.
The following article might be helpful if this is a problem - it goes in a different direction (from MySQL to SQL Server), but has some useful information about the encoding types used in the two databases, as well as some examples of how to do this kind of conversions :
http://agilebi.com/ddarden/2010/09/19/extracting-mysql-utf-8-fields-with-ssis/
source to share
I fixed this issue by changing the ODBC assignment in the Tasks properties. I switched InsertMethod
from Batch
to Row by Row
.
source to share