How do I Loop Through a full resultset  SQL Task and Execute storedprocedure

Posted on 2009-02-13
Medium Priority
Last Modified: 2013-11-10
How do I loop through a full result set returned from SQL task and use the two output parameters returned to pass into a stored procedure. Thanks
Question by:encoredatas
  • 2
  • 2
LVL 17

Expert Comment

ID: 23637460
You need to save the resultset to an Object variable. Create 2 other variables to hold your column data. Connect the Execute SQL Task to a Foreach Loop Container. Choose Foreach ADO Enumerator. Set the Obect source variable to your recordset variable. Choose rows in the first table. Under variable mappings map the column variables to index 0 and 1, assuming the recordset's first two columns are the ones you want.
Now you have it, a loop over those two values.

Author Comment

ID: 23639997
Do I drag the New SQL Task that executes. The stored procedure using the Output variables inside the Foreach Loop Container. I have done this and  I don't seem to see it executing the stored procedure inside the Loop Container.  
LVL 17

Accepted Solution

HoggZilla earned 2000 total points
ID: 23640818
Yes, put the Execute SQL Task for the stored proc inside the Foreach Loop container. Use a script task to verify your variables are populated as expected.
Here is a screen shot.


Author Closing Comment

ID: 31546541
Thanks so much. I'm starting to understand this stuff now.  Can you please look at the second part of this question which is part of the same ssis package titled. Thanks a million!!

"How do I Execute stored procedure and  if true(1) is returned Execute another stored procedure"

Expert Comment

by:ken hanse
ID: 24045660
I am interesting about the learning SSIS packeges. and i'll testing sample on my system.
I like to see the actual files for me to test on my enviroment.

Thank for your help.


Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question