For / ForEach Loop

In DTS, say if we had to work on a group of files or iterate through a list of objects, we had to write code for that in the script objects. It used to be very tedious and time consuming. SSIS however, provides us with two new controls, ForEach Loop and For Loop.


1] ForEach Loop

1. Drag and drop a ForEach Loop control from toolbox.

2. You can select appropriate choice in the Enumerator dropdown. Say if you wish to loop through a set of files at a folder location, you need to select "ForEach File Enumerator" option .

3. You can set a number of options on Collections tab. On click of The Expressions value box, a ellipsis appears. On click of the ellipsis, you can set a number of properties.

4. You can set the DirectoryPath variable to set the path to the folder you wish to scan Or set the "FileSpec" property for the file extensions or file suffix of the file you wish to look for in the form of a regular expressions. You can keep it general as "*.*" too.

5. On the Variable Mappings, select the name of the variable in which you wish to store the name of the object currently being processed through the loop. It can be used as reference for processing inside the loop. The datatype of the variable should match with the object datatype.

6. You can use other all the controls inside the ForEachLoop. It acts as a parent container for the nested controls.

2] For Loop

1. The For Loop works in runs through the loop multiple times in similar way like For Each Loop.

2. You can set the Assign Expression, Init Expression and Evaluation expression for the For Loop.

3. You can set a variable value at the start of the loop using "InitExpression".

4. The loop starts with this value and on completion of cycle executes the expression set in the "AssignExpression " expression.

5.It then evaluates the condition set in the "EvalExpression " expression. Until this condition is not true, it will continue to run the loop. When this condition evaluates to true, The loop stops and moves to next step if any.

6.You can use other all the controls inside the For Loop. It acts as a parent container for the nested controls.