OLE-DB Command Transformation

by Allan Mitchell 2 Aug 2004 14:00

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.

DataFlowComplete

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.

p1

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.

p2

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

p3

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.

Comments (11) -

11/26/2008 7:22:08 AM #

susheel

can some one help me out on ole db command transformation

and at the same time forloop containers and what task we will use in this

susheel India

4/24/2009 1:37:37 PM #

Vasanth

Great post man... keep it up...

Vasanth India

8/26/2009 2:29:28 PM #

Nacho

Very usefull, thanks a lot from Uruguay

Nacho Uruguay

12/28/2009 10:33:30 PM #

CrazyGuy

Thank you

CrazyGuy United States

7/14/2010 11:48:03 PM #

Prash

Dude, that's real good post but could you clear a doubt of mine which is related to this component(OLEDB Command Transformation). The parameter values what you are passing to the store proc in Oledb Command Component are in the flow from some source table right....

     My question is Can I pass a variable value as parameter to this Store Proc in our OleDb Command Component. I guess there is some way we could do this, can you help me with this


            Cheers,,,,,,

Prash United States

7/15/2010 9:05:24 AM #

Darren Green

Prash, Yes the parameters you map are the columns in the data flow. If you wanted to use a variable value then you would need to get this into a column, which is easily done using the Derived Column Tx. Of course the value will be the same for every row, but often that is all you want.

Darren Green United Kingdom

7/21/2010 8:40:51 PM #

Prash

I think now its my time to ask questions regarding this task, I am trying to pass 3 parameters to a proc in OLE DB Command Transformation of datatypes Datetime, decimal and integer.

             I am getting error  **** [OLE DB Command [178]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E07.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E07  Description: "Error converting data type varchar to numeric.". ***

               Could somebody help me with this

Prash United States

10/14/2010 11:42:20 AM #

Davor

Nice post.
I am having one problem. OLE DB command does not recognize parameters I'm using in store procedure. I've even tried to enter external columns manually, but I'm always getting the same error: "bla, bla, bla.... syntax error, permission violation or other nonspecific error..."
It looks like SSIS doesn't see store procedure at all.
The same OLE DB command works fine on three different SQL SERVERS, but on fourth (they are all the same version of SQL) it doesn't work.
Any idea?
Thanks

Davor Croatia

11/1/2010 3:14:13 PM #

Tatyana

Thanks for this post, before it I actually thought that you cannot call for a stored procedure from OLE DB command... However, I still was receiving an error message - "data type clash". I found another discussion (social.msdn.microsoft.com/.../26d130f8-74dd-4649-bb38-5ebdcd5f498a) where this problem was explained: "...Any task that uses an OLEDB connection and passes a parameter that is defined as "Date" will fail with the below error. Changing the parameter to "DATETIME" resolves the issue." I changed a parameter in my stored procedure from "Date" to "Datetime" type, and now the procedure is called from OLE DB command within a Data Flow, and this makes my life so much easier! (Before that I was using a dataset saved into an object variable, and then an "Execute SQL" task outside of Data Flow!..)

Tatyana United States

3/25/2011 10:21:30 AM #

Ashok

Thanks Allen. It really helped me lot.

Ashok United States

6/25/2012 2:05:24 PM #

aravind

Thanks !!!!!!! Solved my problem .

aravind India

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading