Monday 9 May 2011

Foreach Loop based on Variable - SSIS

There are lots of times that you need to set your own enumerator in foreach loop container.
For example you want to select special sub directories based on specified conditions and then you want to loop through them. so you need to make your own enumerator. so it's better to create a variable which contains array of these values and then just loop in the items of this variable.
You know there are few types of enumerators that you can use in foreach loop,
If you want to make your own enumerator based on Variable, you must choose Foreach From Variable Enumerator .


Let me simplify this type of enumerator by an example:
1- Create two variables:
Name                     DataType
Collection               object
Item                      object


2- add a Script Task, set User::Collection as ReadWriteVariables


3- choose language as Visual C#, and write this code in main() method:
public void Main()
        {
            System.Collections.ArrayList arr = new System.Collections.ArrayList();
            arr.Add("the first item");
            arr.Add("the second item");
            arr.Add("the third item");
            Dts.Variables["User::Collection"].Value = arr;

            Dts.TaskResult = (int)ScriptResults.Success;
        }
4- save and build the Script.
5- add a Foreach Loop container, set enumerator as Foreach From Variable Enumerator.
6- in enumerator configuration , select variable User::Collection .

7- in variable mapping tab, set variable User::Item with index 0 .

8- add another Script Task inside the foreach loop container,
9- set language as Visual C#, set ReadOnlyVariable as User::Item .


10- write this script in main() method, and then save and build script:
public void Main()
        {
            MessageBox.Show(Dts.Variables["User::Item"].Value.ToString());

            Dts.TaskResult = (int)ScriptResults.Success;
        }
11- Run the package.

After running the package , you will see message box three times, each times with on statement.
That's All.

No comments:

Post a Comment