ssis mysql to server,SSIS MySQL复制表到SQL Server

I want to copy tables from MySQL to SQL Server.

ADO.NET source using the MySQL connection.

OLE DB destination, SQL Server.

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.

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

How to connect and load data from MySQL to SQL Server

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 - 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 – Create a new SSIS package and create an ADO.NET connection manager AND ADO.NET SQL Server destination.

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 - Run the package and should run and load normally.