SQL Server Integration Services (SSIS) - OLE DB Command Task in SSIS

Introduction

In this article, we are going to see how to use the OLE DB Command Task in SSIS packaging. An OLE DB Command task is mainly used for a set of transformations 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 to be mapped to the table as an external source. Let's jump start to see this sample of how to set the properties of the control.

To follow my series of articles on SSIS, go through my profile.

Steps

Follow steps 1 to 3 in my first article to open the BIDS project and select the right project to work on an 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 during 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 into the source table so that we will see a real time example 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 an OLE DB task we need to create a stored procedure which takes 3 values as input and processes a simple insert statement in the destination table with minor 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; for that 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 it 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 screen below:

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 to build and execute our package. Press F5 to build the package and execute the it. The screen will look as shown 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.