SSIS: Foreach Loop Container

      Using Foreach loop container we can create repeating loops in control flow. Number of loops are depends on source information.

Sample package creation:
Example1:
     I am having a folder, in that folder few source files are available, with same column names, same number of columns and data types. These files i need to load it into a table.

     This requirement we can achieve using "Foreach loop container" and "Data flow task". We need to specify source folder and file information in the container. We need to please "Data flow task" inside the container. Then the container verify the folder and it will create loops. Number of loops depends on source files in the specified folder. If folder having 4 files, container will loop 4 times. For each iteration it will pick one one file and it updates require variable with file path. Using this variable path data flow task will process one by one file.

Source Folder:

Source files data:

Target table creation Script:
                        CREATE TABLE SAMPLE_TBL
                        (
                        ID INT
                        ,NAME VARCHAR(50)
                        ,PRICE NUMERIC(12,4)
                        ,DESCRIPTION VARCHAR(100)
                        )

Package Creation:

Open "BIDS"
From "Control flow items" drag and drop the "Foreach Loop Container".

Edit the "Foreach loop container" and go to "Collection". From the Enumerator drop down list select "For Each File Enumerator". Because we need to process files.

Folder: Browse the source folder using "Browse" button or copy paste the source folder path.
File: If it is "*.*" then "Foreach loop container" will process all files. If we put *.txt then it will process only text files. According to requirement we need to mention.
Retrieve file name: Here three options are available.

Fully qualified: If we select this option then Foreach loop container will pick the one by one file information (File full path including extension example: "D:\Programs\Forum\Source\Source\SourceFile1.txt) and it will assign to required variable.
Name and extension: If we select this option then container will pick one by one file name along with extension and it will assign to specified variable. Example: SourceFile1.txt
Name only: Container will pick one by one file name only. example: SourceFile1
Traverse subfolders: If the required folder is having sub folders and those folders also having source files, then we can select this option.

Variable Mapping: Go to variable mappings (Click on "Variable mappings")
Here we need to specify a variable. This variable dynamically will update, with one by one file information.
Click on variable drop down list and select "<New Variable..>" option.
This it will open "Add Variable" window.

Give the Variable name (SourceFilePath) and Value type (File path is a string variable so we need to select String).
Click on "OK" Button.
Click on "Foreach Loop Editor" "OK" butto.
We can see the created variable in the "variable list".

Go to control flow tool box, drag and drop the "Data flow task" and place inside the "Foreach Loop Container".

Edit the "Data Flow Task". From "Data Flow Sources" drag and drop "Flat file source" and configure with any one of the source file.
From "Data Flow Destinations" drag and drop "OLE DB Destination" and configure with "Flat File Source".

Go to Control flow.
Now the source connection (Flat file source connection) is static. We need to make the source connection dynamic by assigning the variable "SourceFilePath".
Right click on Source connection manager and select "properties" option.

Go to properties window.
Click on "Expressions" and edit.

"Property Expressions Editor" Window will open.
In "Property" Drop down list select "ConnectionString" and edit the Expression.

"Expression Builder" will open.
Click on "Variables" (On + sign). Then it will list all the available variable (System defined and user define variables).
From the variable list drag and drop the "SourcePath" Variable into expression box.

Then click on "OK" Button.
Then after click on "Property Expressions Editor" window "OK" Button.
If we run the package "Validation Errors" will come. Because "SourceFilePath" variable value will come on run time only. So we need to set "Delay validation" property to "True" then validation errors will not come.
To set this property: Don't select any task or container in the package, right click on plain and select properties.