Loops through a series of fi les or records in a data set, and then executes the tasks in the container for each record in the collection.
Enumerator
An enumerator specifies the collection of objects that the container will loop through.
The Foreach loop container supports the following enumerator.
1.Foreach File Enumerator : Performs an action for each file in a directory with a given file extension
2.Foreach Item Enumerator: Loops through a list of items that are set manually in the container
3.Foreach ADO Enumerator : Loops through a list of tables or rows in a table from an ADO recordset
4.Foreach ADO.NET Schema Rowset Enumerator : Loops through an ADO.NET schema
5.Foreach From Variable Enumerator : Loops through a SQL Server Integration Services (SSIS) variable
6.Foreach Nodelist Enumerator : Loops through a node list in an XML document
7.Foreach SMO Enumerator : Enumerates a list of SQL Management Objects (SMO)
Foreach Loop container commonly Used for executing a set of operations repeatedly based on an enumerable collection of items (such as files in a folder, a set of rows in a table, or an array of items).
TRY IT
Loop through a collection of flat files in a directory and load them to a SQL table
Configuration.
- Specify the path of the files directory.
- Specify what kind of file extension the file have.(eg. for text files '*.txt').
- Create a variable 'FileName' to store the location of the file name while looping.
- Use the above created variable as an expression for the file connection manager that points to the flat file.
Because the flat files each have a different name, you can use the Variable
Mappings tab in Foreach loop editor to dynamically change a variable value for each iteration of the loop. That variable then can pass the correct file name to the Flat File Connection with an expression.
Steps :
1.Create a new package
2.Drag a Data Flow Task onto your designer
3.Create a new Flat File Connection Manager,and point it to the first file. Also, check the
Column names in the first data row option and go to the Columns page to ensure all the columns
are defined properly; then click OK.
4.In the Data Flow, bring a new Flat File Source. Open the Flat
File Source Editor by double-clicking the Flat File Source and make the connection manager
the newly created one. Then click OK.
5.Next, create an OLE DB Connection Manager, using the AdventureWorks2012 database.
6.Bring an OLE DB Destination in the Data Flow and connect the Data Flow path
from the source to it.
7.Drag a Foreach Loop Container.
8.Place the Data Flow Task inside the Foreach Loop Container; then open the Foreach Loop
Editor by double-clicking the top banner portion of the container. On the Collections tab,
select Foreach File Enumerator from the Enumerator property drop-down box. The Foreach
File Enumerator is the default when you open the editor.
9.Set the folder property.for bring back all text files in the directory set Files Property to *.txt.
10.Create a new variable.
11.in the variable mapping tab,select the variable name.
Each time the container runs the variable value will change to the current file.
12.The last step is to put an expression on the File Connection Manager that uses
the variable you just created inside the Foreach Loop Container. Select the connection managers and press F4 to bring up the Properties window. Click the ellipsis next to the Expressions property to open
the Property Expressions Editor. Select ConnectionString from the Property drop-down and
then click the ellipsis in the Expression box.
13.In the top left of the Expression Builder, expand the Variables and Parameters folder and
drag the variable down into the Expression box. If you try to click Evaluate
Expression now, there will be no result. Remember that this expression will be populated at
run time, so you will see nothing here yet.
14.The package is now complete.A succesful run will loop through all the files and load to the table.
Forloop container and Foreachloop conatainer
For Loop iterates through the content of the container a number of times you define or you define with an expression, whereas the Foreach Loop Container iterates for every member of the enumerator.If there are no members, the container will not execute.
