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.

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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.