Hadi Fadlallah
for loop flow diagram

SSIS Foreach Loop vs For Loop Container

December 4, 2019 by

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:

  1. Initialization
  2. Evaluation
  3. 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:

for loop flow diagram

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:

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#:

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.):

SSIS for loop container description from the toolbox

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

SSIS for loop container editor

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:

SSIS foreach loop container description from the toolbox

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

Selecting the enumarator type within the SSIS foreach loop container collection tab page

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.

SSIS foreach loop container variable mappings tab page

Figure 6 – SSIS Foreach Loop Container variable mappings tab page

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:

Table of contents

SSIS OLE DB Source: SQL Command vs Table or View
SSIS Expression Tasks vs Evaluating variables as expressions
SSIS OLE DB Destination vs SQL Server Destination
Execute SQL Task in SSIS: SqlStatementSource expression vs Variable Source Type
Execute SQL Task in SSIS: Output Parameters vs Result Sets
SSIS Derived Columns with Multiple Expressions vs Multiple Transformations
SSIS Data types: Change from the Advanced Editor vs Data Conversion Transformations
SSIS Connection Managers: OLE DB vs ODBC vs ADO.NET
SSIS Flat Files vs. Raw Files
SSIS Foreach Loop vs For Loop Container
SSIS: Execute T-SQL Statement Task vs Execute SQL Task

Hadi Fadlallah
17,770 Views