A comment on my post about http://blogs.msdn.com/mattm/archive/2009/01/07/writing-to-a-mysql-database-from-ssis.aspx#9453805 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 http://technet.microsoft.com/en-us/library/ms141138.aspx transform to execute the SQL statement on a row by row basis. Since the preferred http://blogs.msdn.com/mattm/archive/2008/03/03/connecting-to-mysql-from-ssis.aspx 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 http://msdn.microsoft.com/en-us/library/ms141172.aspx you can cache the rows you need to update/delete in a package variable, and then http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx 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 http://agilebi.com/cs/blogs/jwelch/archive/2008/10/21/a-merge-destination-component.aspx 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!
