0

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 !

Improve this question
4
  • possible duplicate of Updating records in MYSQL with SSIS Commented Apr 10, 2014 at 16:58
  • This post explains how to update data from SQL Server to MySQL by using SSIS. However, due to constraints about connectors (ADO.NET / OLE DB) the process is more complex to import from MySQl to SQL Server than from SQL Server to MySQL. Commented Apr 11, 2014 at 9:17
  • Can't you just do the same thing, in reverse? Using an OLE DB target connection for the SQL Server database in a staging table, then running an UPDATE or MERGE against the final table? Commented Apr 11, 2014 at 13:15
  • Ok. So, first of all I need to create a new SQL Server connection to store my staging table, then to connect the matching rows output (of the Lookup component) to a new destination OLE DB compoment (linked to my staging table). Secondly, I need to add a new data flow to read the staging table, to execute a OLE DB command to update content. However, I don't understand how to link these to steps. Should I crete two data flows and add a priority constraint to execute the import before the export ? Moreover, how can I create the staging table with SSIS and troncate it after at the end ? Thanks. Commented Apr 11, 2014 at 14:26

2 Answers 2

Reset to default
1

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

1 Comment

It seems the only way to use a OLE DB command is to change to a OLE DB data source. However, MySQL is not compatible with OLE DB data sources. That's why I used a ADO.NET source to grab data from MySQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.