CODEWITHSUNDEEP
mysqlsql-serverssisado.net
K4timini
K4timini

Reputation: 741

How to insert/update rows from MySQL to SQL Server by using SSIS

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

enter image description here

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 !

Upvotes: 0

Views: 2052

Answers (2)

N West
N West

Reputation: 6819

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)

Upvotes: 1

Mike Honey
Mike Honey

Reputation: 15027

I would change the SQL connection to use OLE DB. As well as allowing the OLE DB Command to work, you may also find the OLE DB Destination is faster.

Upvotes: 0

Related Questions