SSIS MySQL复制表到SQL Server [英] SSIS MySQL Copying Table to SQL Server

问题描述

我想将表从MySQL复制到SQL Server.

I want to copy tables from MySQL to SQL Server.

使用MySQL连接的ADO.NET源.

ADO.NET source using the MySQL connection.

OLE DB目标SQL Server.

OLE DB destination, SQL Server.

该声明是完整副本，因此没有什么大不了的.当我按下播放"按钮时，过一会儿会弹出一个错误.我设置了一个DataViewer并重新开始，在检查DataViewer时从DataFlow内部运行它.过了一会儿(这次花了更长的时间)错误.

The statement is a full copy so no big deal there. When I hit the play button, after a while an error pops up. I set a DataViewer and started all over again, running it from inside the DataFlow while checking the DataViewer. After a while (this time it took longer) the error.

错误:数据流任务处出现0xC02090F5，ADO NET源 1 :组件 "ADO NET Source"(1)无法处理数据.致命错误 数据读取过程中遇到的问题.
错误:数据流任务处为0xC0047038， SSIS.Pipeline:SSIS错误代码DTS_E_PRIMEOUTPUTFAILED.这 组件"ADO NET Source"(1)上的PrimeOutput方法返回错误 代码0xC02090F5.当 流水线引擎称为PrimeOutput().故障代码的含义 由组件定义，但错误是致命的，流水线 停止执行.在此之前可能会发布错误消息 并提供有关失败的更多信息.

Error: 0xC02090F5 at Data Flow Task, ADO NET Source 1: The component "ADO NET Source" (1) was unable to process the data. Fatal error encountered during data read.
Error: 0xC0047038 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "ADO NET Source" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

对这个错误有任何想法吗?

Any idea on this error?

推荐答案

这花了我几天的时间才弄清楚...所以我想我会分享我的笔记

this took me few days to figure out...so I thought I would share my notes

如何将数据从MySQL连接和加载到SQL Server

How to connect and load data from MySQL to SQL Server

1-下载32位ODBC驱动程序. 请访问MySQL网站并下载:"mysql-connector-odbc-5.2.4-ansi-win32.msi".注意:请勿在BIDS 2008上使用64位驱动程序.BIDS2008是32位.创建SSIS的连接管理器时会出现不匹配错误:指定的DSN包含驱动程序和应用程序之间的体系结构不匹配"

1 - Download the 32 bit ODBC driver. go to the MySQL website and download: "mysql-connector-odbc-5.2.4-ansi-win32.msi" NOTE: Do not use the 64 bit driver on BIDS 2008. BIDS 2008 is 32 bit. You will get a mismatch error when creating SSIS’s connection manager: "The specified DSN contains an architecture mismatch between the Driver and Application"

2-创建用户DSN您需要打开使用Windows 32 ODBC管理工具.不要在控制面板中打开常规的ODBC管理.打开位于以下位置的ODBC管理:c:\ Windows \ SysWOW64 \ odbcad32.exe.如果您使用默认的ODBC admin，它将不起作用.此外，您必须创建用户DSN"-而不是系统DSN.否则，它将不会在SSIS中显示.注意:屏幕外观相同，因此您将无法知道自己是否在32位ODBC Admin工具中.

2 - Create a User DSN You need to open the using windows 32 ODBC admin tool. DO NOT open the regular ODBC admin, in control panel. Open the ODBC admin located here: c:\Windows\SysWOW64\odbcad32.exe. If you use the default ODBC admin…it will not work. Additionally you must create a "User DSN" - NOT a System DSN. Otherwise it will not show up in SSIS NOTE: the screens look the same so you will have no way of knowing whether you are in 32 BIT ODBC Admin tool or not.

3 –创建一个新的SSIS包并创建一个ADO.NET连接管理器和ADO.NET SQL Server目标.

3 – Create a new SSIS package and create an ADO.NET connection manager AND ADO.NET SQL Server destination.

4 –更改Source ADO.NET属性.您将收到验证错误，并且程序包将无法运行.您需要将ADO.NET源的"ValidateExternalMetadata"更改为FALSE(在高级编辑器"对话框中)，这也会给您带来元数据错误……没关系……只需单击确定"即可.它仍然会提取元数据(列名/数据类型).您不能像在SQL Server中那样选择表.您需要键入SQL select语句.

4 – Change the Source ADO.NET properties. You will get validation errors and your package will not run. You need to change the "ValidateExternalMetadata" to FALSE (in the "Advanced Editor" dialog box) of the ADO.NET source It will also give you metadata error…that’s ok…just click ok. It will still pull the metadata (column names/data types). You cannot select the tables as you would in SQL server. You need to type the SQL select statement.

5-运行程序包，应该可以正常运行和加载.

5 - Run the package and should run and load normally.

这篇关于SSIS MySQL复制表到SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！