OLE-DB Command Transformation

In the newsgroups we have been asked on a number of occasions how to execute a stored procedure once for every row of input data. There's currently no really good way of doing this but in SQL Server 2005 DTS that is about to change. This article is going to show you how to take values from a source table and for every row execute a stored procedure which inserts values into another table.

This package is really quite simple. It involves only one Data Flow task in the Workflow and two components in the Data Flow, an OLE DB Source adapter and the OLE DB Command Transformation. As always let's start by having a look at the end result which in our case is the struture of the Data Flow.

We are going to need a couple of tables and a stored procedure for this article so let's create them now.

CREATE TABLE SourceParametersForStoredProc
(  
	Param1 int,  
	param2 int
)

CREATE TABLE DestinationOLEDBCommandDest
(  
	dest1 int,  
	dest2 int
)

CREATE PROCEDURE dbo.DoubleInputValues 
	@input1 int, 
	@input2 int
AS
SET NOCOUNT ON

INSERT dbo.DestinationOLEDBCommandDest 
(
	dest1,
	dest2
)  
VALUES
(
	@input1 * 2, 
	@input2 * 2
)
GO

Now let's enter some data so we can at least show it's working

INSERT SourceParametersForStoredProc 
VALUES(1,2)

We now click through to the Data Flow section and we drop onto designer an OLE DB Source adapter from our toolbox. We need to configure this to point to our source table SourceParametersForStoredProc. Next we drop onto the designer an OLE DB Command transformation and we hook up the OLE DB Source Adapter. Go ahead and double click on the OLE DB Command destination now. You will note that on the first tab it is asking you to specify a Connection Manager (Data Source). We need to point that at the location of our stored procedure created earlier as this is where we will be doing the work. The following graphic shows us having done this.

On the next tab Component Properties we will be setting the SQL Command (SqlCommand) that will be executed for each row of data in the source. In our case it is our stored procedure with two parameters denoted by "?". You can also set a timeout on the command in this tab as well.

Before moving from the Component Properties tab to the Input Columns tab make sure you hit the refresh button down the bottom. Now on clicking the Input Columns tab you will note that it's name changes to Mappings. On this Mappings tab you can map your input columns to the correct parameters in your stored procedure

The package is now complete. There promises to be a million uses for this Transformation so watch out for more articles in which it is involved.