ssis mysql to server,如何通过使用SSIS将行从MySQL插入/更新到SQL Server

I'm looking for the best practice to insert or update rows from a MySQL connection to a SQL Server connection.

First of all, I added a ADO.NET data source to grab MySQL content (a simple table Supplier with two fields id and name). Then, I added a Lookup transformation to split new rows / updated rows. It works well when I need to insert new rows. However, I would like to use a Command OLE DB to update existing rows but It doesn't work due to a incompatibility between my connection manager and the component (ADO.NET vs OLE DB).

Any idea to update modified rows ?! Should I use a cache component ?!

Thanks in advance !

解决方案

Just get rid of the lookup and conditional split all together.

Outside of your SSIS package, build a staging table that contains the fields you need for inserts/updates.

In your SSIS Package, create a control flow that does the following:

Execute SQL Task to truncate the staging table.

Data Flow task to load the MySQL data from the source system to the staging table. If you can do this based on a "changes-only" type process, such as using a timestamp that you check, it would be faster.

Execute SQL Task to perform an UPDATE statement on your target table using the staging table joined to the target table.

Execute SQL Task to perform an INSERT statement on your target table using a query based on the target table and your staging table (with a WHERE NOT EXISTS or some such on a key fied)