Floating FB popout byF5debug

SQL Server Integration Services (SSIS) – Part 60 – OLE DB Command Task in SSIS

 

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.

[more]

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 - http://f5debug.net/tutorial/ssis.aspx

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

clip_image001
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
clip_image002
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
clip_image003
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
clip_image004
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
clip_image005
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
clip_image006
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
clip_image007
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
clip_image008
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
clip_image009
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
clip_image010
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
clip_image011
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
clip_image012
This indicates that the execution is completed and we can see the desired output in the table destination as shown in the screen below
clip_image013

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.

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Comments (2) -

Dante Delfi
Dante Delfi
8/31/2013 5:50:22 AM #

I'm very pleased to discover this great site. I wanted to thank you for your time just for this wonderful read!! I definitely enjoyed every little bit of it and i also have you book marked to look at new stuff on your web site.

Information AboutUltrasoundTechnician
Information AboutUltrasoundTechnician
9/5/2013 2:50:01 AM #

Due to this ever increasing need of international income transfer services, there have been many mediums introduced which provide fast and price efficient services. <a href="http://theultrasoundtechnician.com/diagnostic-medical-sonographers/">How Much Is An Ultrasound Technician Make</a>. The card recipient can add further funds anytime you want. Online funds transfer is the paramount alternative for on the web firms.

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter