SSIS Package - For Each Loop Container - SQL Programmers chicago

Get a free consultation

Need help with Microsoft SQL Server? Ask our Experts, or simply Email your query.

About Sql Server Programmers

Our clients can be found both locally in the Chicagoland area and throughout the country. We have over 18 years of experience and are extremely proud of our track record of successfully assisting hundreds of our clients to improve their productivity while focusing on cost.

Microsoft Certified Partner

SSIS Package For Each Loop Container

The For Each Loop Container defines a repeating control flow in the package. Loop implementation in the For Each Loop Container is similar to the Foreach looping concept in various programming languages. The Foreach enumerator enables looping in a package.

There are several different enumerator types available.  They are:

Foreach File Enumerator

The File Enumerator enumerates files in a folder. For example, we can get all the files which have the *.txt extension in a windows folder and its subfolders.

Foreach ADO Enumerator

The ADO Enumerator enumerates rows in a table. For example, we can get the rows in the ADO records.

Foreach ADO.Net Schema Rowset Enumerator

The ADO.Net Enumerator enumerates the schema information. For example, we can get the table from the database.

Foreach Item Enumerator

The Item Enumerator enumerates the collections. For example, we can enumerate the names of executables and working directories that an “Execute Process” task uses.The Item Enumerator enumerates the collections. For example, we can enumerate the names of executables and working directories that an “Execute Process” task uses.

Foreach Nodelist Enumerator

The Node List Enumerator enumerates the result of an XPath expression.

Foreach From Variable Enumerator

The Variable Enumerator enumerates objects that specified variables contain. Here enumerator objects are nothing but an array or data table.

Foreach SMO Enumerator

The SMO Enumerator enumerates SQL Server Management Objects (SMO). For example, we can get the list of functions or views in a SQL Server database.

Example: Foreach File Enumerator

Here we walk through an example for the Foreach File Enumerator.

Using the Foreach File Enumerator, we are going to move the files from one folder to another folder. Assume that our source folder is D:\Docs and our destination folder is C:\TmpDocs.

Open Visual Studio, and create a new integration service project. After creating the integration service project you can see there is a default “package1”. Rename the package to “ForEachFileExample”.

SSIS For each loop container

Double click the “ForEachFileExample” package to initiate the design mode. Now we have to add a variable. Right click the form, then click variables from the menu.

SSIS for each loop

The variable explorer will be displayed in the left side of the design. From the window click the “Add Variable” option.

SSIS For each loop

Add the Name of the variable as “DocVariable”, Data Type as “String” and Value as “default”.

SSIS For each loop

Drag and place the ForEach Loop Container from the tool box to the form.

SSIS For each loop

Now the Foreach Loop container will be placed in the form.

  • Double click on the Foreach Loop container box.
  • The Foreach loop editor window will be displayed.
  • Click the collection from the left side window.
  • Make sure the Enumerator Type is “Foreach File Enumerator”; if not, select it.
  • Set the source folder in the Enumeration Configuration section.
  • I have taken D:\Docs as the source folder.
  • Click “Traverse Subfolders” checkbox.

Your settings will be similar to the image below:

SSIS For each loop

Click the Variable Mappings option from the left hand side panel.

Select the variable User::DocVariable.

SSIS For each loop

Click OK.

Drag the File System Task into the Foreach Loop container.

SSIS For each loop

The File System Task Editor window will be displayed.

Set the following properties in the Editor:

  • Set IsDestinationPathVariable to False.
  • Click the Destination Connection Dropdown. Select the New Connection option.
  • Select “Existing Folder” option in the Usage Type Dropdown box.
  • Then select the Destination folder using “Browse” Button.    
  • Here I selected C:\ TmpDocs.

SSIS For each loop

Click OK to continue.

Set “OverwriteDestination” to “True” and change the “Operation” type to “Move File”. Also set “IsSourcePathVariable” as “True” and select the “SourceVariable” as “User::DocVariable”.

SSIS For each loop

Click OK.

Now run the package. The files will be moved from the D:\Docs folder to C:\TmpDocs folder.

Handling Exceptions in an SSIS Package >