What is " rows in all the tables" in for each loop?

Question

When setting up a for each loop to read products from an "objProduct" object variable, I got three options in "Enumerator Mode" pane as snapshot shows:

I know "Rows in the first table" is the right option for current case. However, I'm curious in which scenarios will the second and third options be used?

Seems that "ADO Object Source Variable" will contain multiple tables if 2nd/3rd is applied. That's confusing... shouldn't one variable be regarded as one table and thus, only the first option is needed?

P.S. I did researches and only MSDN sheds some light as below, but not quite clear when they will be applied and for what purpose.

**Rows in all tables (ADO.NET dataset only)**
Select to enumerate rows in all tables. This option is available only if the objects to enumerate are all members of the same ADO.NET dataset.
**All tables (ADO.NET dataset only)**
Select to enumerate tables only.

Schick

I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". – Phallus

Thank you! Will pay more attention to it next time :-) – Schick

Answer 1

Let's say that you execute the following SQL in an Execute SQL Task (using an ADO.NET connection) and you store the full result set in an SSIS Object variable.

select * from
(select 1 as id, 'test' as description) resultSet1
;
select * from
(select 2 as anotherId, 'test2' as description union
 select 3 as anotherId, 'test3' as description) resultSet2

That object is actually a System.Data.DataSet, which can contain multiple result sets (accessible via the Tables property). Each of those result sets is a System.Data.DataTable object. Within each result set (or System.Data.DataTable) you have rows.

The Rows in all tables (ADO.NET dataset only) and All tables (ADO.NET dataset only) options can be used when you need to iterate through all the result sets (instead of just the first one). The difference between the two is what objects are being enumerated over.

Rows in all tables (ADO.NET dataset only) - take all the rows of data returned from the SQL above and go through them one by one, mapping the column values to variables specified in your Variable Mappings. For the example above, you would have 3 total iterations (3 total rows). This behavior in a Script Task would look something like this:

All tables (ADO.NET dataset only) - take all the result sets from the SQL above and go through them one by one, mapping the result set to the variable specified in Variable Mappings. For the example above, you would have 2 total iterations (2 total result sets). This behavior in a Script Task would look something like this:

I've never had the need to use either one of these options, so I can't provide any specific scenarios where I've used them.

Gobioid

Welcome to the StackOverflow and the SSIS tag. This is a fantastic answer and I look forward to many future ones like this ;) – Coad

Thanks so much for this wonderful answer! Clear and elegant. I thought Object(System.Data.DataSet) will not distinguish each result set within it. Hmm, seems it's much smarter than I expected :-) – Schick

I'm sure I'll have the same question down the road, I just hope I can find my way back to this page :) – Gobioid

I would like to know more about this. I tried this but doesnt work. I can give you an example. I have a store procedure that has many queries and each query returns an xml object. Each query has the for xml clause. I cant do union on that so I was wondering on how to iterate many result sets. Tried the example you gave but didnt work... – Antifreeze

@Gobioid please see my question here #45611557 – Antifreeze