无法使用 SSIS 将 SQL Server varchar(max) 传输到 MySQL 文本 [英] Unable to transfer SQL Server varchar(max) to MySQL text using SSIS

问题描述

我正在尝试使用 SSIS 将表从 SQL Server 传输到 MySQL，但它在 SQL Server 上的 varchar(max) 和 MySQL 上的文本的一列上失败.到目前为止，我已经尝试将其作为 OLE DB 到 ODBC 数据流，以及 BCP 输出到平面文件到 ODBC 数据流.有问题的字段在 SSIS 中配置为 DT_TEXT.在这两种情况下，它都会产生以下错误集:

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 目标 [47]] 错误:发生开放式数据库连接 (ODBC) 错误.插入第 1 行时 SQLExecute 返回错误

[ODBC Destination [47]] Error: Open Database Connectivity (ODBC) error occurred. SQLExecute returned error while inserting row 1

[ODBC 目标 [47]] 错误:发生开放式数据库连接 (ODBC) 错误.状态:'HYC00'.本机错误代码:0.[MySQL][ODBC 5.2(a) 驱动程序][mysqld-5.1.69-log]不支持带有执行数据的参数数组

[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] 错误:SSIS 错误代码 DTS_E_PROCESSINPUTFAILED.过程输入组件ODBC 目标"(47) 上的方法失败，错误代码为 0x80004005，而处理输入ODBC 目标输入"(58).识别出的组件返回了一个ProcessInput 方法中的错误.该错误特定于组件，但错误是致命的，会导致数据流任务停止运行.可能有错误在此之前发布的消息包含有关失败的更多信息.

[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.

我还想指出，以前这是使用带有 openquery 的链接服务器完成的，并且没有任何问题.但是，由于性能问题，我们正试图摆脱链接服务器.

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.

更新:

我设法使用 SQL Server BCP out 和 MySQL LOAD DATA INFILE 来完成所需的行为，但知道如何使用 SSIS 完成此操作仍然很好数据流，因为它绝对是更简单的方法，并且应该更快，因为它在内存中运行.

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.

推荐答案

确保源和目标中的文本编码相同，否则您在 SSIS 中进行适当的转换.

Make sure that the text encoding is the same in the source and destination, or that you're converting suitably in SSIS if not.

如果这是问题所在，以下文章可能会有所帮助 - 它正朝着另一个方向(从 MySQL 到 SQL Server)，但提供了一些关于两个数据库中使用的编码类型的有用信息，以及一些示例如何进行此类转换:

The following article might be helpful if that is the issue - it's going in the other direction (MySQL to SQL Server), but has some useful information on the types of encoding used in the two databases, as well as some examples of how to carry out conversions of this kind:

http://agilebi.com/ddarden/2010/09/19/extracting-mysql-utf-8-fields-with-ssis/

这篇关于无法使用 SSIS 将 SQL Server varchar(max) 传输到 MySQL 文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！