SSIS ForEach Loop Containers Explained

By Tom Nonmacher

In the world of SQL Server Integration Services (SSIS), the ForEach Loop Container is a powerful tool that enables users to iterate through a collection of objects or data rows. This is particularly useful when you need to execute a task multiple times with different input values. In this post, we will delve into the specifics of SSIS ForEach Loop Containers and how to leverage them effectively with SQL Server 2019, MySQL 8.0, DB2 11.5, Azure SQL, and Azure Synapse.

The ForEach Loop Container functions as a repeating control flow in your package. It repeats the control flow for each member of a specified enumerator. The enumerator could be a set of files in a folder, a set of rows in a table, or a set of nodes in an XML file, among others. This functionality can simplify a lot of processes, such as applying transformations to multiple data sources or loading a set of flat files into a database.

For instance, if you are working with SQL Server 2019, and your task is to load multiple text files into a database, you can configure a ForEach Loop Container to enumerate the files in a specific directory. Inside this container, you can then include a Data Flow Task that reads each file and loads the data into your database. Here's an example of how you might define this in T-SQL: 


DECLARE @FileName varchar(255)
DECLARE @Path varchar(255)
SET @Path = 'C:\YourDirectory\'
DECLARE FileCursor CURSOR FOR
SELECT name FROM sys.fn_dirFiles(@Path, '*.txt')
OPEN FileCursor
FETCH NEXT FROM FileCursor INTO @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
    -- your data loading process here
    FETCH NEXT FROM FileCursor INTO @FileName
END
CLOSE FileCursor
DEALLOCATE FileCursor

Similarly, in MySQL 8.0, you can use a ForEach Loop Container to iterate through a result set from a query, and perform operations on each row. This is accomplished by using a cursor to fetch each row and a loop to process it. Here is a MySQL example: 


DECLARE cur CURSOR FOR SELECT column_name FROM table_name;
DECLARE @value VARCHAR(255);
OPEN cur;
FETCH NEXT FROM cur INTO @value;
WHILE @@sqlstatus = 0 DO
    -- your processing code here
    FETCH NEXT FROM cur INTO @value;
END WHILE;
CLOSE cur;

In DB2 11.5, you can use a similar approach to iterate through a result set. The following example shows how to declare a cursor, open it, fetch rows from it in a loop, and then close it when you're done: 


DECLARE cur CURSOR WITH HOLD FOR SELECT column_name FROM table_name;
DECLARE @value VARCHAR(255);
OPEN cur;
FETCH FROM cur INTO @value;
WHILE (SQLCODE = 0) DO
    -- your processing code here
    FETCH FROM cur INTO @value;
END WHILE;
CLOSE cur;

For Azure SQL and Azure Synapse, you can also use the ForEach Loop Container to loop over a result set and perform operations. However, keep in mind that while Azure SQL supports stored procedures and T-SQL scripts, Azure Synapse does not. Therefore, you might need to use alternative methods, such as Azure Data Factory or Azure Functions, to achieve the same result in Azure Synapse.

In summary, the ForEach Loop Container in SSIS is a versatile tool that can be used to iterate over a collection of objects or data rows. It can be effectively employed in a variety of databases including SQL Server 2019, MySQL 8.0, DB2 11.5, Azure SQL, and Azure Synapse. The power of the ForEach Loop Container lies in its ability to simplify complex tasks and increase the efficiency of your data integration processes.

Check out the latest articles from all our sites:




828C2D
Please enter the code from the image above in the box below.