SSIS Foreach Loop Container

Like Foreach loop statement in programming languages, Foreach loop container realizes the same function in SSIS package and it repeats running the control flow in the container for each member of the defined enumerator. The enumerator can be configured in the collection tab in Foreach Loop Editor. The following is a list of all enumerators which SSIS current supports.

Foreach File enumeratorEnumerate files in a folder.Folder, Files, File name and if Traverse sub-folders
Foreach Item EnumeratorEnumerate items you defined.IteFolder, Files, File name and if Traverse sub-folders
Foreach ADO EnumeratorEnumerate rows in tables.ADO object source variable and Enumeration mode
Foreach ADO.NET Schema Row set EnumeratorEnumerate the schema information of a data source.Connection and Schema
Foreach From Variable EnumeratorEnumerate the objects in a variable.Variable
Foreach NodeList EnumeratorEnumerate the result set of an XPath expression.Document and XPath Settings.
Foreach SMO EnumeratorEnumerate SQL Server Management Objects (SMO) objects.Connection manager and Enumerate.

Let's create a sample package to copy all the TXT files from a source folder C:\SSIS and its sub-folders to a destination folder C:\SSIS_dst. To finish the package, we'll use the sub-folders which were created in the previous section and create t1.txt in F1, t3.txt in F3 and t5.txt in F5. The TXT files exist in the folder C:\SSIS as follows.

Source File Locations

Now follow the steps below to create the package.

  1. Create an empty package ForeachLoopContainer.dtsx in the LearnSSIS1 project and open it in design mode.

  2. Drag & drop Foreach Loop Container to the package and drag & drop File System Task in the container.

  3. Create a variable FullName with string type and "aaa" value in order to hold each file name in the loop.

  4. Right click the container and choose "Edit..." to open the Foreach Loop Editor window, click Collection tab and set the configuration below.
  5. Foreach Loop Editor Collection Tab

    The file name can be defined as one of the following.

    • Name and extension: The file name and its extension only. For example, test.txt.
    • Fully qualified: The fully file name including its path. For example, C:\SSIS\test.txt.
    • Name only: The file name without the extension. For example, test.

  6. Click Variable Mappings tab, choose "User::FullNmae" in the drop-down list of Variable and the Index 0 will be added automatically.
  7. Foreach Loop Editor Variable Mappings Tab

    The variable will hold the fully qualified file name for each loop.

  8. Click OK button to finish the container setting. Then right click File System Task, choose "Edit..." to open File System Task Editor and finish the configuration as follows.
  9. File System Task Editor Copy File

  10. Choose in DestinationConnection field to open File Connection Manager Editor and finish the setting below.
  11. SSIS File Connection Manager Editor

  12. Click OK twice to finish all the setting. Then run the package.
  13. After running the package, all the TXT files in the folder C:\SSIS and sub-folders are copied to the destination folder.

Note Note
The variable FullName must hold a non-empty value otherwise the validation will not be passed and an error message will be issued.