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:
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.
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.
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!
Use NET/Connector from Mysql. Download and install always the latest version.
with regard to the Script Option - I still can't get this to work. I get syntax errors as soon as my script (SQL FileConnection) contains more than one SQL statement. For example my source file looks like:-
-- first truncate the table
-- Next populate the basic fields from the source table
I can't figure why this should be