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.

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.

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.

For details about the package Lookup.dtsx, please check the previous section.



3. 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.





4. 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.





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





6. 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".





7. Remove all the rows in the middle and click OK.





8. Right-click "Data Conversion" and choose "Edit..." to open its editor and change it as follows.



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



9. 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.





10. Click Connection tab and click "Use a table or a view" and choose the student table.





11. Click Columns tab, Link the "Copy of Student Name" to the "name" in the lookup dataset and add id as a new output column.





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





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


Select

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.



14. Click OK button. Then click refresh button to let system know how many parameters are defined.





15. Click "Column Mappings" tab and in the Input Column, choose id for Param_0 and "Copy of Student Name" for Param_1.





16. Click OK button and run the package. After execution, click "Stop Debugging" and check the database Test.



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