Nested For Each


I'm not sure if is possible in SSIS but I have 2 DataSet's and I would like to do a nested loop.


For Each Row in DataSet1 (loop through each header)
          For Each Row in DataSet2 (loop through the details that are equal to the header)

To help you understand DataSet1 is the Headers and DataSet2 is the Details.  Additionally the Header Rows are each numbered and the Detail Rows have the unique header row number in them.

I know I can do this in Scripting tasks but I just wondered if there was a proper/elegant way of doing it in SSIS.

Any help greatly appreciated.

Who is Participating?
raulggonzalezConnect With a Mentor Commented:
Why not create 1 dataset with all the rows?

And you can loop just once.

hope it helps.

FROM Headers AS H
ON D.headerID = H.ID

Open in new window

EvilPostItConnect With a Mentor Commented:
If you are not using a SQL data source and are in fact importing from files etc then you have the following options.

1)   Yes you can do a nested loop although you will have to do it using variables. Also this will be row based and could take longer if there a many rows.
2)   You may be able to get the results you are after by doing this in a data flow task using the merge or merge join transformations.

It really depends on what you want to do. For example you have not specified if you want to perform and action for each rows.

If you could give a bit more detail as far what you want the entire package to do i should be able to give a more precise answer.
if this is .net datatables and datasets then you can set up and use data relations to return all the child rows for a parent
hydevAuthor Commented:
You have both given me ideas to possible solutions so thanks.  
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.