locked
Feeding a Foreach loop with an Execute SQL statement RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I am stuck on a passing variable issue. I am fairly new to SSIS, but I believe that what I am trying to accomplish can be executed with an Execute SQL Task and a Foreach loop with a data flow task.

    Using an SSIS package in SQL 2008.
    I have a table with the following 3 fields: ID (number), Hyperlink (string), Category (number)
    The hyperlink is to an XML file online that I need to download.
    However, this table is often changing, so I want to dynamically call through a select statement then pass each field in a variable to the Foreach loop.
    Then in the loop, I want to use the fields of each row of data returned to download the XML file one after another.

    So I think this is possible, and I can manually use a basic data flow task to download and store all of the hyperlinks I have, I am just attempting to automate this with the loop. Is this possible? Are there any good examples of using a recordset returned from a SQL Task to variables in a Foreach loop? I can provide what I have but was hoping for some direction from this forum.

    Thanks,

    Spike

    Saturday, November 14, 2009 11:44 PM

Answers

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote
    You can use execute sql task capturin the result set in an object variable. Then usin for each loop with for each ado enumerator.
    Check this
    http://sqlblog.com/blogs/louis_davidson/archive/2007/01/19/ssis-foreach-looping-through-a-recordset.aspx
    http://pragmaticworks.com/community/blogs/mikedavis/archive/2008/12/19/ssis-for-each-ado-enumerator-loop.aspx

    There is a video by Brian Knight on jumpstart  : http://www.jumpstarttv.com/Media.aspx?vid=38
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Proposed as answer by Bharani 3010 Sunday, November 15, 2009 3:34 AM
    • Marked as answer by Spike00 Monday, November 16, 2009 1:27 PM
    Sunday, November 15, 2009 2:09 AM
  • Question
    Sign in to vote
    1
    Sign in to vote
    In your package create four variables:

    ID as an Int32
    Hyperlink as a String
    Category as an Int32
    Recordset as an Object

    Create a connection manager to your server.

    Drop a SQL task on the control flow work surface, set the connection manager to the one you just created. Set the ResultSet property to "Full result set." Set the SQLStatement property to:

    SELECT ID, Hyperlink, Category FROM <whatever your table name is>;

    Click on Result Set on the left side of the Execute SQL Task Editor. Click Add.

    Set the Result Name to 0. That's the number zero.

    Set the Variable Name to: User::Recordset. Click Ok.

    Drop a For Each Loop container on the work surface. Connect it to your SQL Task and bring up the Foreach Loop Editor by double clicking the container.

    Click on Collection. The set the Enumerator to "Foreach ADO Enumerator."

    In the Enumerator configuration area, set the ADO object source variable to User::Recordset, and click "Rows in first table."

    Click on Variable Mappings on the left side of the Editor. Set the variables, in order, to:

    User::ID with an index of 0.
    User::Hyperlink with an index of 1.
    User::Category with an index of 2.

    And finally, click OK.

    The Foreach Loop will execute for each row in the table, with the values from the current row in the three variables created above. Add whatever other tasks you need to the Loop container.

    I hope this helps.





    • Proposed as answer by Bharani 3010 Sunday, November 15, 2009 3:34 AM
    Sunday, November 15, 2009 2:22 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks very much for the response! Video link provided was exactly what I was looking for! Always easy to watch someone else do it then just replicate! Thanks again!
    Monday, November 16, 2009 1:27 PM