Execute stored procedure for each row in the table in SSIS

Question

I would like to execute a stored procedure MyProc with certain input parameters for each row in the table MyTable. Column for each row will act like input values for MyProc.

How do I accomplish this in SSIS? I have been told by upper management to use SSIS and I don't have any choice in this matter.

So.. I have done the following:

Create package
Create Execute SQL Task
- Created query inside ExecuteSQLTask
Create Foreach Loop Container
- Create Data Flow Task inside Foreach loop container.
- Inside Data Flow Task I created OLE DB Source
- Connected OLEDBSource to Derived Column
- Enable DataViewer in connection in step 3.3

It looks like the DataFlowTask in step 3.1 is being called for all rows in step 2.1

What I want is to invoke DataFlowTask in step 3.1 to get called for each record in the row in step 2.1 AS OPPOSED TO all the records.

Once this works, then I intend to ExecuteSQLTask inside a ForEachLoopContainer.

enter image description here

I am so confused by your question. Even the edits were rolled back. Perhaps, we start afresh. What is the problem you are trying to solve? — billinkc
– billinkc, Commented Apr 8, 2015 at 16:51
I can't really think of any reason why you would want this. Can you explain what the reasoning behind this is. What does MyProc do? — JodyT
– JodyT, Commented Apr 8, 2015 at 16:57
billinkc, its a very simple issue. MyTable lets say returns 3 rows with ColA and colB. I want MyProc to be executed 3 times with input parameter values of ColA and ColB. The way I have it right now, is that DataFlowTask in ForEachLoopContainer is being called 9 times instead of 3 times. — dotnet-practitioner
– dotnet-practitioner, Commented Apr 8, 2015 at 17:07
The other approach to Tab's fine answer is to remove the Data Flow Task and replace it with an Execute SQL Task that calls your stored procedure. — billinkc
– billinkc, Commented Apr 8, 2015 at 19:47

Tab Alleman · Accepted Answer · 2015-04-08 18:05:04Z

8

All you need is a Dataflow Task with an OLEDB source and an OLE DB Command transformation.

The OLEDB source SELECTs from the table that you want to perform the row-by-row stored procedure on.

It then is followed by an OLE DB Command transformation that calls the stored procedure and passes columns from the data flow to the parameters of the stored procedure.

This will result in the stored procedure being executed once for each row of your table. You don't need the initial Execute SQL, and you don't need the For-Each loop.

edited Apr 8, 2015 at 18:05

answered Apr 8, 2015 at 17:43

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dotnet-practitioner Over a year ago

I can not find Execute SQL transformation in dataflow task any where

dotnet-practitioner Over a year ago

Hi Tab, do you know, how I can iterate the above DataFlowTask 200 times? Basically, I want to do the following 200 times: 1) select records from the table 2) execute stored proc based on that select statement as input... How would I do it?

Tab Alleman Over a year ago

If you mean you need to do this to 200 records, you can modify your OLEDB Source query to return the 200 records that you want. If you need to do 200 different SELECTs, then in that case you would need to put the DataFlow task in a ForEachLoop container, and use a variable to count to 200.

Collectives™ on Stack Overflow

Execute stored procedure for each row in the table in SSIS

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related