Updating records in MYSQL with SSIS

I am writing an SSIS package that has a conditional split from a SQL Server source that splits records to either be updated or inserted into a MYSQL database.

• The开发者_运维百科 SQL Server connection has provider .NET Provider for OldDB\SQL Server Native Client 10.0.

• The MYSQL connection is a MYSQL ODBC 5.1 ADO.NET connection.

I was thinking about using the OLE DB Command branching off of the conditional split to update records but I connect use this and connect to the MYSQL database.

Does anyone know how to accomplish this task?

I would write to a staging table for updates including the PK and columns to be updated and then execute an UPDATE SQL statement using that table and the table to be updated. The alternative is to use the command for every row and that just doesn't seem to perform that well in my experience - at least compared to a nice fat batch insert and a single update command.

For that matter, I guess you could do without the conditional split altogether, write everything to a staging table and then use an UPDATE and INSERT in SQL back to back.

Probably, the following MSDN blog link might help you. I haven't tried this.

How do I UPDATE and DELETE if I don’t have an OLEDB provider?

The post suggests the following three options.

1. Script Component
2. Store the data in a Recordset
3. Use a custom component (like Merge destination component)

The author also had posted two other articles about MySQL prior to posting the above article.

Connecting to MySQL from SSIS

Writing to a MySQL database from SSIS

Hope that points you in the right direction.