SSIS: Foreach Loop Container

The Foreach Loop container defines a repeating control flow in a package. It is similar to Foreach looping in programming languages. In a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.

This procedure describes how to configure a Foreach Loop container, including property expressions at the enumerator and container levels.

  1. In SQL Server Data Tools (SSDT), open the Integration Services project that contains the package you want.
  2. Click the Control Flow tab and double-click the Foreach Loop.
  3. In the Foreach Loop Editor dialog box, click General and, optionally, modify the name and description of the Foreach Loop.
  4. Click Collection and select an enumerator type from the Enumerator list.
  5. Specify an enumerator and set enumerator options
  6. Optionally, click the browse button (…) in the Expressions text box on the Collection page to create expressions that update property values. 
  7. Optionally, click Variable Mappings to map object properties to the collection value, and then do the following things:
    a. In the Variables list, select a variable or click <New Variable> to create a new variable.
    b. If you add a new variable, set the variable properties in the Add Variable dialog box and click OK.
    c. If you use the For Each Item enumerator, you can update the index value in the Index list. Note, The index value indicates which column in the item to map to the variable. Only the For Each Item enumerator can use an index value other than 0.
CategoriesKB

Leave a Reply

Your email address will not be published. Required fields are marked *