CODEWITHSUNDEEP
sql-serverssisetl
dotnet-practitioner
dotnet-practitioner

Reputation: 14148

Execute stored procedure for each row in the table in SSIS

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:

  1. Create package
  2. Create Execute SQL Task
    • Created query inside ExecuteSQLTask
  3. 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

Upvotes: 3

Views: 8098

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

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.

Upvotes: 8

Related Questions