Use Recordset Destination as input in a Foreach Loop Container

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.