how to use foreach with "for each from variable enumator" if variable is of List<> type in SSIS packages.
12
votes
1 Answers
21
votes
You have to declare two SSIS variables
- the collection variable (source for the For each enumerator)
- the variable for one item (used within the enumerator)
Let's say you have a List<string>
and you need to iterate through its items.
Here is a sample how to do it:
- in SSIS variables window create variable named "col", type "object"
- create variable named "s", type "string"
create a sample script task that will fill the "col" collection and add the "User::col" variable to list of the tasks ReadWriteVariables. The script body would be following:
List<string> col = new List<string>() {"One", "Two", "Three"}; Dts.Variables["User::col"].Value = col;
create a Foreach loop container and configure it to type "From variable enumator" over variable "User::Col".
- in the Foreach container variable mappings add a mapping for the "User::s" variable
create a sample script task within the Foreach container, demonstrating consuming of the iteration (add the "User::s" to task's ReadOnlyVariables). The script body would be following:
string val = (string)Dts.Variables["User::s"].Value; MessageBox.Show(val);
- execute the sample by pressing F5 in BIDS. It should display three dialog boxes with texts "One", "Two", "Three".
Note: the script samples are written in c# for BIDS 2008.