SSIS Package For Each Loop Container

Next Post
By Admin at 4 Nov 2014, 13:00 PM
  • 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”.


    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.


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


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


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


    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:


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

    Select the variable User::DocVariable.


    Click OK.

    Drag the File System Task into the Foreach Loop container.


    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.


    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”.


    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 >



    Post a comment

    Please correct the following: