How do I do UPDATE and DELETE if I don’t have an OLEDB provider?
A comment on my post about writing to a MySQL database inspired me to put together this post about how to perform UPDATEs and DELETEs in an SSIS package.
The common approach is to use the OLEDB Command transform to execute the SQL statement on a row by row basis. Since the preferred methods of connecting to MySQL don’t involve using OLEDB providers, even if you’re willing to pay the performance penalty of processing your data row by row, you can’t use the OLEDB command.
I’d suggest three alternatives:
Script Component
Ahh, the trusty script component. Using the same ADO.NET Connection Manager you’re using for your MySQL database, you can use the script to execute the statements you need.
Store the data in a Recordset
Use a Recordset Destination you can cache the rows you need to update/delete in a package variable, and then process it in your control flow using a For Each Loop and Execute SQL Task.
Use a custom component
If you don’t mind installing a custom extension, John Welch’s Batch Destination component is a great alternative here. It will stage your rows in a temporary table, and then execute a SQL statement to process the data in a single batch. From a design perspective, this should perform better than the first two approaches (batch vs. row by row).
Hope that helps!
