12
votes

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

1
Which language are you using?YWE

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:

  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.