The Foreach Loop containter can use ADO recordsets as enumerator. The ADO recordset is stored in a variable of type ‘Object’. Often the ADO recordset is populated with an Excecute SQL Task. But when the data that needs to be enumerated in the Foreach Loop container must be found by combining 2 databases on different servers, the use of an Excecute SQL Task is less obvious.. A solution is to create a DataFlow that populates a Recordset Destination. This Recordset destination is an ADO recordset that is stored in a variable of type ‘Object’. Therefore it can be used as the enumerator in a Foreach Loop Container.
Example
We need to send data from a central database to different on separate locations. The separate server information is stored in a configuration database. The Data to send to the servers comes from different database with sales and financial data.
ADO recordset stored in a Variable
Variable in the screenshot is called ‘AdoRecordset’ in namespace ‘User’ and of value type ‘Object’.
Using the Execute SQL Task
The Execute SQL Task will execute a select statement (or a stored procedure) that collects the necessary data that will be used to cycle through in the Foreach Loop Container. In this case it will read all servers and their connection data.
Excute SQL task general setting
ResultSet : full result set
Connectiontype ADO.Net (can be Oledb as well)
Execute SQL Task Result Set setting
Result Name: 0
Variable Name: AdoRecordset
Foreach Loop Container Collection settings
Enumerator: ‘Foreach ADO enumerator’
ADO object source variable: User::AdoRecordset, check ‘rows in the first table’.
Foreach Loop container Variable Mappings
Here the columns in the recordset need to be mapped to variables in the package.
Variables are created as needed
Variable: ServerId, Index 0
Variable: ConnectionString, Index 1
The connectionString variable
Is used as an expression in a Connection object. The connection object will be used in various dataflow inside the Foreach Loop container
Remote server Expression properties: use @User::ConnectionString as ConnectionString property.
This will allow to use the same connection in each iteration of the foreach loop with another connectionstring.
Using the Recordset Destination in a Dataflow instead of Execute SQL Task
Instead of a single select statement in an Execute SQL Task we will use a DataFlow with a combination of various sources to combine in a Recordset Destination. The Recordset destination is stored in the variable AdoRecordset.
In the control flow the Execute SQL Task is replaced by a Data Flow Task.
The Data flow task reads data from several databases and combines it into a Recordset Destination
The recordset Destination editor
VariableName: AdoRecordset.
Conclusion
When the Ado recordset for a foreach loop is composed of data from various databases or other Data Flow sources, or when complex decision logic is needed, use a Data Flow with a Recordset Destination instead of an Execute SQL Task to create an in memory recordset that is consumed by the foreach Loop container.
