I'm trying to transfer a table from SQL Server to MySQL using SSIS and it is failing on one column that is varchar(max) on SQL Server and text on MySQL. So far I have tried doing this as an OLE DB to ODBC data flow, as well as BCP out into a flat file to ODBC data flow. The field in question is configured in SSIS as DT_TEXT. In both instances, it is producing 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 select to ignore the column both versions of the package run fine.
I would also like to note that previously this was done using a linked server with openquery and it worked without any problems. However, due to performance issues we are trying to move away from linked servers.
UPDATE:
I managed to accomplish the desired behavior using SQL Server BCP out followed by MySQL LOAD DATA INFILE but it would still be nice to know how to accomplish this using an SSIS data flow, since it's definitely the easier way to do it and should be faster since it operates in memory.
