In this article, first, we will briefly describe foreach loops and for loops. Then, we will give an overview of SSIS Foreach Loop Container and For Loop Container. Finally, we will compare these two containers.
This article is the tenth article in the SSIS feature face-to-face series, which aims to remove confusion and compare and contrast similar features provided by SQL Server Integration Services.
Introduction
In general, loops are used to execute one or more statements repeatedly until a specific condition is satisfied. They are one of the most basic statements in programming and are implemented in a wide range of programming languages such as Java, Python, Visual Basic, C#, SQL and others. In addition to programming languages, loop functions are implemented in data warehousing and data preparation technologies such as SQL Server Integration Services.
There are different types of loops, such as:
- While
- Do…While
- For
- Foreach
In this section, we will briefly describe the for loop and foreach loop since this will make understanding SSIS Loop Containers easier.
For Loop
For loop is a counter-based loop that is composed of three main phases:
- Initialization
- Evaluation
- Counter increment
In the first phase, an initial value is assigned to the counter. This value is used in the first evaluation phase. In the evaluation phase, if the evaluation condition result is false, the loop will end; if true, it will execute the statements within the loop, then increment the counter and go back to the evaluation phase. The following diagram shows the for loop flow:
Figure 1 – For loop flow diagram
As an example, the following code is a simple for loop clause written in C#. In this loop, the counter is initiated at 0, the evaluation phase is to check if the counter is less than 10, and after each loop the counter is incremented by 1:
|
1 2 3 4 5 |
for(int i=0;i<10;i++){ // statement must be written here } |
Foreach Loop
A foreach loop is very similar to a for loop, except there is no evaluation phase. This is because you are looping over a collection of objects, and for each object in the collection, you execute a specific statement. As an example, you have a list of names and you need to loop over these names without the need for an evaluation phase or to use any counter (not index-based loop).
Case in point, the following code is a foreach loop clause that loops over a string located in a list of strings in C#:
|
1 2 3 4 5 |
foreach(string str in list){ // to do statement } |
- Note: For more information about for/foreach loops, refer to this article: w3schools.com – C# For Loop
SSIS Loop Containers
After describing for loops and foreach loops, we will show how these operations are implemented within SSIS. There are many methods to implement loops within SSIS within different scopes:
-
Control Flow
- SSIS For Loop Container
- SSIS Foreach Loop Container
- Within a script task
-
Data Flow Task
- Within a script component
In this section, we will not focus on how to implement loops using scripting in SSIS. Instead, we will give an overview of the containers mentioned above.
SSIS For Loop Container
SSIS For Loop Container has the same context of for loops, as we mentioned above. It has three phases: Initialization, Evaluation and Counter Increment. But, the statements to be executed within a For Loop Container in SSIS are in the form of SSIS tasks (Execute SQL Task, Script Task, File System Task, Execute Process Task, etc.):
Figure 2 – SSIS For Loop Container description from the toolbox
When we open the SSIS For Loop Container editor, there are three main properties that we have to set:
- InitExpression: What is the initialization expression?
- EvalExpression: What is the evaluation expression?
- AssignExpression: What is the counter incremental expression?
Variables must be added within the SSIS package, and to use them, you must add the @ character as the prefix. As example, if we added the @[User::Counter] variable, to implement a for loop similar to For(int i=0;i<10;i++){},we must use the following expressions:
- InitExpression: @Counter = 0
- EvalExpression: @Counter<10
- AssignExpression: @Counter = @Counter + 1
Figure 3 – SSIS For Loop Container editor
- Note: To learn more about SSIS For Loop Container, refer to the following official documentation: SSIS For Loop Container
SSIS Foreach Loop Container
The SSIS Foreach Loop Container is more complicated than the For Loop Container since it has many use cases and requires a more complex configuration:
Figure 4 – SSIS Foreach Loop Container description from the toolbox
There are different types of enumerators in the SSIS Foreach Loop Container. You can select the enumerator type from the collection tab within the SSIS Foreach Loop Container editor form:
- Foreach item enumerator: Loop over a set of items that can be defined manually within the SSIS Foreach Loop Container editor
- Foreach File enumerator: Loop over files within a specific directory
- Foreach ADO enumerator: Loop over file rows in an ADO Recordset
- Foreach ADO.NET Schema Rowset enumerator: Loop over schema information from a specific data source (tables in a database)
- Foreach from Variable enumerator: Loop over items stored within an SSIS variable of type object (must be enumerable)
- Foreach NodeList enumerator: Loop over a result set of an XML Path Language (XPath) expression
- Foreach SMO enumerator: Loop over SQL Server Management Objects (SMO) objects, such as available servers
- Foreach HDFS File enumerator: Loop over files located within a Hadoop distributed file system directory
- Foreach Azure Blob: Loop over blobs in a blob container in Azure Storage
- Foreach ADLS File: Loop over files in a directory in Azure Data Lake Store
- Foreach Data Lake Storage Gen2 File: Loop over files in a directory in Azure Data Lake Store Gen2
Figure 5 – SSIS Foreach Loop Container collection tab page
Each enumerator has its own properties that we must configure.
To catch the current item while looping over a collection, we must add an SSIS package and map this variable to the item within the SSIS Foreach Loop Container variable mappings tab page by specifying the item index within the current row and the variable name.
Figure 6 – SSIS Foreach Loop Container variable mappings tab page
- Note: For more information about SSIS Foreach Loop Container, refer to the official documentation: SSIS Foreach Loop Container
Getting the loop iteration number
By using the SSIS Foreach Loop Container, we can get the current item using variable mappings, but we cannot know the iteration number. To do that, we must add an expression task or a script task with an incremental variable. For more information, refer to the following answer on Stackoverflow.com: Display foreach loop iteration number in SSIS
SSIS Foreach Loop Container Vs. For Loop Container
With this description of both containers, you can see that each has its own use case. In this section, I will mention some examples of using each container.
One of the most efficient uses of SSIS For Loop Container is loading data as chunks; this approach is used to minimize the memory load when handling a huge volume of data. You can refer to our previously published article for more information: SQL OFFSET FETCH Feature: Loading Large Volumes of Data Using Limited Resources with SSIS.
SSIS Foreach Loop Container is more widely used than the Fr Loop Container since it can perform more popular actions, such as looping over files within a directory or looping over an Execute SQL Task result set. The following articles contain some examples of using SSIS Foreach Loop Container:
- Loop through Excel Files and Tables by Using a Foreach Loop Container
- How to import text files with the same name and schema but different directories into database?
- Looping Through a Result Set with the Foreach Loop
Table of contents
Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.
On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.
Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.
View all posts by Hadi Fadlallah
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023