SSIS OLE DB Command Transformation

The OLE DB Command transformation is used to run an SQL statement for each row of its input data flow to insert, update or delete records in a database table. The transform has one input, one output and one error output. Generally speaking, its input data flow is always defined as arguments to feed the parameters defined in the SQL statement of the transform.

Let's see an example, assume several students were graduated from the college and their borrowed books were returned. It's time to remove their records in lendout table and student table in Test database. The contents of the 2 tables look like below.

[Lend out Table] [Student Table]

Normally we'll use student ID as the key to remove records but in this example to make things clear, I'll use student name as a key and assume there is no student with the same name. So an excel file graduate.xlsx in folder C:\SSIS was created below.

[Graduate Excel File]

Now follow the step by step to the create a new package to use the OLE DB Command transformation.

  1. Open the project LearnSSIS2 which was created in the previous section in Visual Studio 2012.

  2. Make a copy of the package Lookup.dtsx and rename the copy to OLEDBCommand.dtsx. Then open the package and click "Data Flow" to open it editing surface.
  3. For details about the package Lookup.dtsx, please check the previous section.


  4. The Input Output Selection dialogue box will be popped up, we'll choose "Lookup Match Output" to link the OLE DB Command Input. Then Click OK.
  5. [Add OLE DB Command]


  6. Right-click Excel Connection Manager and choose "Edit..." to open its editor and choose the excel fil to C:\SSIS\graduate.xlsx, then click OK button.
  7. [Excel Connection Manager]


  8. Right-click Excel Source and choose "Edit..." to open its editor and click "Columns" to see the columns were changed already.
  9. [Excel Source Columns]


  10. Click OK button. You will see a red cross shown up on the output of Excel Source. Then right-click the output and choose "Resolve References".
  11. [Resolve References]


  12. Remove all the rows in the middle and click OK.
  13. [Remove Incorrect Columns]


  14. Right-click "Data Conversion" and choose "Edit..." to open its editor and change it as follows.
  15. [Data Conversion Editor]

    The "Student Name" was converted to a new column "Copy of Student Name" with string type.


  16. Click OK. Right-click Lookup transform and open it editor and change the "Redirect rows to no match output" to "Fail component" in the General tab.
  17. [Lookup Editor General]


  18. Click Connection tab and click "Use a table or a view" and choose the student table.
  19. [Lookup Editor Connection]


  20. Click Columns tab, Link the "Copy of Student Name" to the "name" in the lookup dataset and add id as a new output column.
  21. [Lookup Editor Columns]


  22. Click OK. Right-click OLE DB Command and choose "Edit..." to open its advanced editor. Then select localhost.Test as the Connection Manager.
  23. [Select Connection Manager]


  24. Click Component Properties tab and select SqlCommand and click "..." to input the SQL command like below.

  25. DELETE FROM lendout
    WHERE id = ?
    
    DELETE FROM student
    WHERE name = ?
    

    The question marks stand for parameters and their names are Param_0, Param_1 and so on. In the following steps, we'll map them to the input columns.


  26. Click OK button. Then click refresh button to let system know how many parameters are defined.
  27. [SqlCommand]


  28. Click "Column Mappings" tab and in the Input Column, choose id for Param_0 and "Copy of Student Name" for Param_1.
  29. [Parameter Mappings]


  30. Click OK button and run the package. After execution, click "Stop Debugging" and check the database Test.
  31. [Result In Database]

    The graduate students' records were removed from the 2 tables.