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

Posted on 2009-02-13
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
    LVL 17

    Expert Comment

    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

    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

    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

    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
    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now