Parameters in a foreach Cycle

  • Greetings,

    I am experimenting a bit with the "new" DTS and testing I found the following questions with the foreach cycle:

    I have a query that gets data from a table, something like: "select ID, Name, X, Y FROM Data By Order ID".

    I want to process each record by its ID in the foreach cycle.

    How to specify the container that the cycle variable to evaluate in each iteration is ID?

    Thank you

  • Hi xavendano

    First You need to have the resultset of the inital query going into an SSIS object variable you create.

    Then change the enumerator in the loop container to "For Each ADO Enumerator" (collections page)

    Select your object variable in the dropdown on the same page.

    If you only want the id, select the variable you want it to go into (variable mappings page), and put the index as 0. If you want more fields from the query, just increment the index number and set to different variables.

    This should work for you.

    Cheers

    Laurence

  • Hi, thanks for your response ...

    This query must be OLE DB or ADO.NET?

    What is the ideal for the foreach cycle?

    Thank you

  • It doesn't really matter, the one I have infront of me uses an OLE DB, and this works fine.

    L

  • Hi L,

    I changed the type of outcome "Resulset" and the execution of the task presented to me a mistake.

    [Execute SQL Task] Error: Invalid returned a number of links ResultSetType results for: "ResultSetType_Rowset.

    Do you have any idea what could this be?

    how to fix it?

    xavendano

  • Hi, You need to set the result set to store to a variable, otherwise you will get this error.

    Right click anywhere on the control flow screen and select variables, add one and give it a name and set the data type to "object". Now in the SQL task, go to the resultset page, click add, change result name to "0", and select the variable you have just created. The task should now run.

    Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply