This is part 60 of the series of article on SSIS

Introduction:

In this article we are going to see how to use the OLE DB Command Task in SSIS packaging. OLE DB Command task is mainly used for set of transformation that happen on each row of the SQL command which will be executed using this task. Basically the executed SQL Statements are handled as parameters which will be mapped to the table as an external source.

Let’s jump start to see this sample how to set the properties of the control. You can look into my series of article on SSIS at the url – https://f5debug.net/tutorial/ssis.php

Steps:

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see how to use the OLE DB Command to see the flow. Now once the project is opened drag and drop a source and an OLE DB Command task as shown in the screen below

We can see some red marks on each task which indicates that the tasks are not configured. We need to configure each task so that while execution we can have a smooth process. In our sample we need two tables as source and destination. So we have created 2 tables as shown in the screen below

Scripts:

Create table EmployeeSalarySource
(
EmpSourSalary int,
EmpSourGross int,
EmpSourHRA int
)

Create table EmployeeSalaryDestination
(
EmpDestSalary int,
EmpDestGross int,
EmpDestHRA int
)

Select * from EmployeeSalarySource
Select * from EmployeeSalaryDestination
Now we will insert some data to the source table so that we will see a real time example on the same as shown in the screen below

Now we have a source data table and a destination data table with some sample data in the source table, in order to proceed with our transformation using OLE DB task we need to create a stored procedure which takes 3 values as input and process a simple insert statement in the destination table with small manipulation. So we will create a stored procedure as shown below

Script:

Create procedure dbo.usp_CalculateEmpSalary
@intEmpSourSalary int,
@intEmpSourGross int,
@intEmpSourHRA int

AS

SET NOCOUNT ON

Insert into EmployeeSalaryDestination (EmpDestSalary, EmpDestGross, EmpDestHRA)
Values
(
@intEmpSourSalary * 10,
@intEmpSourGross * 5,
@intEmpSourHRA * 2
)

Now we are ready with the source and destination table with a stored procedure which prepares the transformation steps. Now let’s configure the task step by step as shown in the screens below
First we are going to configure the OLEDB Source, in this we need to specify our source table as shown in the screen below

We can see the mapping table column names by navigating to the tab Columns at the right side menu as shown in the screen below

Now once we are done with the configuration for the Source tables we can see the red mark is removed as shown in the screen below

Now we will configure the OLE DB Command task, we need to double click the same to go the configuration window. Once we double click we will see the window as shown in the screen below

Now we will see how to configure this task. First select the connection manager name using the drop down as shown in the below screen

Now move to the next tab Component properties. Here we need to specify the source command that is to be executed across each row on the component. Since in our case it’s going to be the stored procedure we should select the procedure as shown in the screen below

Now we need to move to the next tab Column Mapping. Here we are going to map the respective columns from the stored procedure to the table so that each will be mapped and the respective columns take care of execution as shown in the screen below

Now we are ready with our package to build and execute it. Press F5 to build the package and execute the same. You can see the screen looks like below

This indicates that the execution is completed and we can see the desired output in the table destination as shown in the screen below

Conclusion:

So in this article we have seen how to use the OLE DB Command task to execute a statement on each row set by set and to get the desired result after manipulation.