The Foreach Loop Container is a fundamental control flow element in SQL Server Integration Services (SSIS). Its primary purpose is to iterate over a specified collection, processing tasks or data flow components within the loop for each element in that collection. This container is particularly useful when dealing with scenarios where you need to perform a set of operations multiple times, such as processing files in a folder or rows in a result set.
The Foreach Loop Container allows you to iterate over various types of collections, including files in a folder, rows in a result set, ADO.NET recordsets, and more
Scenarios and situations where using a Foreach Loop Container is beneficial
- You have a folder containing multiple files, and you want to perform a data load operation on each file.
- You need to extract data from multiple tables in a source database and load it into a destination database.
- You have a result set from a database query, and you need to perform a set of operations for each row.
- You need to process data in different databases with different connection strings.
- You have a specialized collection that doesn’t fit the built-in enumerators.
- You need to dynamically change variable values during each iteration.
- You have a set of tasks or data flow components that need to be executed with custom logic for each iteration.
Explanation of different data types the Foreach Loop supports.
The Foreach Loop Container in SSIS supports several enumerator types, and the choice of enumerator determines the type of collection over which the loop iterates. Each enumerator type supports specific data types. Here are some common enumerator types and the data types they support:
Foreach File Enumerator
Supported Data Types: string: File names, including full paths, are supported. You can use wildcards to filter files. Example Scenario: Iterating over a folder of text files, Excel files, or any other file type.
Foreach Item Enumerator:
Supported Data Types: Object: This enumerator can iterate over a collection of objects, and it’s quite flexible. You can use it for a variety of scenarios. Example Scenario: Iterating over a collection of custom objects or items stored in an object variable.
Foreach ADO Enumerator:
Supported Data Types:ADO Recordset: ADO recordsets, typically obtained from a database query, can be used for iteration. Example Scenario: Iterating over rows in a database table or result set.
Foreach ADO.NET Schema Rowset Enumerator:
Supported Data Types:DataTable: This enumerator works with ADO.NET DataTables, often obtained by querying database schema information. Example Scenario: Iterating over schema information for database tables, columns, or procedures.
Foreach From Variable Enumerator:
Supported Data Types: String, Object, and other simple types: This enumerator can work with different data types depending on what is stored in the specified variable. Example Scenario: Iterating over a list of values stored in a string or object variable.
Foreach Node List Enumerator:
Supported Data Types: XML Node List: It works with XML node lists, allowing you to iterate over nodes in an XML document. Example Scenario: Iterating over XML nodes to extract or process data.
Foreach SMO Enumerator:
Supported Data Types: Server, Database, and other SMO types: This enumerator is specific to SQL Server Management Objects (SMO) and works with SMO objects. Example Scenario: Iterating over SQL Server databases, tables, or other SMO objects.
Foreach Nodelist Enumerator (SSIS 2019 and later):
Supported Data Types: XML Node List: Similar to the Foreach Node List Enumerator, but available in SSIS 2019 and later versions. Example Scenario: Iterating over XML nodes to extract or process data.
Foreach ResultSet Enumerator (SSIS 2019 and later):
Supported Data Types: ResultSet: Works with result sets returned by Execute SQL Task or other tasks that produce result sets. Example Scenario: Iterating over result sets returned by a stored procedure or SQL query.
Example with our tool
In this guide, we’ll leverage SSIS Advanced File System to extract JSON files from a folder and iterate through them using a Foreach Loop Container
Begin by using the Advanced File System task to retrieve all JSON files from a specified folder and store the result in a variable:
Next, set up a Foreach Loop Container and configure the enumerator as ‘Foreach ADO Enumerator.’ Assign the previously created variable as the enumerator variable.
In the Variable Mapping tab, designate an existing variable to hold the collection value
Proceed to include a Logging Task to display the variable’s value in a message box.
Upon running the package, you’ll observe the following result: each iteration of the loop reads a distinct folder path
SSIS Advanced File System and the Foreach Loop Container, you can efficiently process JSON files in a designated folder. This step-by-step guide simplifies the implementation of this workflow, allowing for seamless integration into your SSIS projects