Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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:

enter image description here

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.
like image 682
Echo Avatar asked Dec 31 '13 17:12

Echo


People also ask

What is foreach ADO enumerator in SSIS?

SQL Server Integration Services provides the following enumerator types: Foreach ADO enumerator to enumerate rows in tables. For example, you can get the rows in an ADO recordset. The Recordset destination saves data in memory in a recordset that is stored in a package variable of Object data type.


1 Answers

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.

enter image description here

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:

enter image description here

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:

enter image description here

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.

like image 142
gannaway Avatar answered Jan 02 '23 00:01

gannaway