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

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

  • Comments 2

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!

Leave a Comment
  • Please add 5 and 2 and type the answer here:
  • Post
  • 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:-

    set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES';

    -- first truncate the table

    TRUNCATE contacts.hugh_contacts_transformed;

    -- Next populate the basic fields from the source table

    INSERT INTO

     contacts.hugh_contacts_transformed(

       UID,

       Title,

       `First Name`,

       `Middle Name`,

       `Last Name`................................................................

    I can't figure why this should be

Page 1 of 1 (2 items)