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()

            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.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog:

Leave a Reply