Built on Beta 2

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)

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

INSERT SourceParametersForStoredProc VALUES(1,2)