how to use list collection for “foreach” in SSIS

问题:

how to use foreach with "for each from variable enumator" if variable is of List<> type in SSIS packages.

回答1:

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:

1. in SSIS variables window create variable named "col", type "object"
2. create variable named "s", type "string"

3. 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;
   

4. create a Foreach loop container and configure it to type "From variable enumator" over variable "User::Col".

5. in the Foreach container variable mappings add a mapping for the "User::s" variable

6. 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);
   

7. 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.