Parameters in a foreach Cycle

Question

Post reply

Parameters in a foreach Cycle

xavendano

Mr or Mrs. 500

Points: 552
More actions
July 1, 2009 at 8:19 am

#131759

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

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

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

NaffMonk Old Hand Points: 325 More actions · Answer 1

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

xavendano Mr or Mrs. 500 Points: 552 More actions · Answer 2

Hi, thanks for your response ...

This query must be OLE DB or ADO.NET?

What is the ideal for the foreach cycle?

Thank you

NaffMonk Old Hand Points: 325 More actions · Answer 3

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

L

xavendano Mr or Mrs. 500 Points: 552 More actions · Answer 4

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

NaffMonk Old Hand Points: 325 More actions · Answer 5

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