Sunday, August 28, 2016

For Each Loop Container in SSIS

For Each Loop Container in SSIS:

Why For Each Loop Container Task is Use ?
  • For Each Loop container is falls under container and looping tasks
  • Use containers like the For Each Loop and For Loop to execute a set of tasks multiple times.
  • For example, you can loop over all the tables in a database, performing a standard set of operations like updating index statistics.
  • In short when we have to iteratively execute set of task we will insert all those task under For Loop Container and set the values accordingly
  • The for each loop container acts as a repeating control flow in a package. Its operations are similar to work of For each keyword in any advanced programming language. We have a definite type of enumerator for each type of objects.
  • Loop implementation in the For Each Loop Container is similar to the Foreach looping concept in various programming languages.
The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure 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.
Enumerator is nothing but an iterator where an object that enables a programmer to traverse a container
SQL Server Integration Services provides the following enumerator types:
  1. Foreach File Enumerator: It enumerates files in a folder. The plus point here is it can traverse through subfolders also.
  2. Foreach Item Enumerator: It enumerates items in a collection. Like enumerating rows and columns in an Excel sheet.
  3. Foreach ADO Enumerator: Useful for enumerating rows in tables.
  4. Foreach ADO.NET Schema Rowset Enumerator: To enumerate through schema information about a data source. For example, to get list of tables in a database.
  5. Foreach From Variable Enumerator: Used to enumerate through the object contained in a variable. (if the object is enumerable)
  6. Foreach NodeList Enumerator: Used to enumerate the result set of an XML Path Language (XPath) expression.
  7. Foreach SMO Enumerator: It enumerates through SQL Server Management Objects (SMO) objects.
How For Each Loop Container Task is look like ?
This is Second task in tab itself !!
image
Drag this out in your development plane
image
Features of For Each Loop Container Task
The following diagram shows a Foreach Loop container that has a File System task. The Foreach loop uses the Foreach File enumerator, and the File System task is configured to copy a file. If the folder that the enumerator specifies contains four files, the loop repeats four times and copies four files.


image
Where we can use a combination of variables and property expressions to update the property of the package object with the enumerator collection value. First you map the collection value to a user-defined variable, and then you implement a property expression on the property that uses the variable.
image
Enumerator Types
    image
    How to use For Loop Container Task ?
    We can configure For Each Loop Container in following three ways
    1. SSIS Foreach Loop Container – General
    2. SSIS Foreach Loop Container – Collection
    3. SSIS Foreach Loop Container – Variable Mappings
    image
    SSIS Foreach Loop Container – General
    Use the General page of the Foreach Loop Editor dialog box to name and describe a Foreach Loop container that uses a specified enumerator to repeat a workflow for each member in a collection.
    In this tab we can just specify name of for each loop container as we required :
    image
    SSIS Foreach Loop Container – Collection
    Here we can actually select various enumeration and iterate accordingly
    See the following list when we select collection tab and Click on dropdown Enumerator
    image
    Following three way to configure this tab :
    1.Select Enumeration Type
    image
    2.Select Path path or variable list for main Enumeration in 7 option
    Here we are getting different option for every enumeration
    (I) For each File Enumeration
    Select Path of the file and type/extension for required file to traverse
    image
    (II)For Each Item Enumerator
    Select or defined the item as show in configuration plan
    image 
    (III)For Each ADO Enumerator
    In this we have three option as follows
    image
    (IV)Foreach ADO.NET Schema Rowset Enumerator
    To enumerate through schema information about a data source. For example, to get list of tables in a database.
    image
    (V)Foreach From Variable Enumerator
    Here we have choose variable or we can defined new variable too
    image
    (VI)Foreach NodeList Enumerator
    Used to enumerate the result set of an XML Path Language (XPath) expression.
    image
    (VII)Foreach SMO Enumerator
    It enumerates through SQL Server Management Objects (SMO) objects.
    image

    In Short Select the enumerator type from the list. This property has the options listed as follows:
    1. Foreach File Enumerator: Enumerate files
    2. Foreach Item Enumerator: Enumerate values in an item
    3. Foreach ADO Enumerator: Enumerate tables or rows in tables
    4. Foreach ADO.NET Schema Rowset Enumerator: Enumerate a schema
    5. Foreach From Variable Enumerator: Enumerate the value in a variable
    6. Foreach Nodelist Enumerator: Enumerate nodes in an XML document
    7. Foreach SMO Enumerator: Enumerate a SMO object
    SSIS Foreach Loop Container – Variable Mappings
    In this last tab we are going to just map the variable as we required
    As I have map Var:User:MyFirstVar in index 0
    image
      Various Properties for For Each Loop Container Task ?
      Following are various configurable properties for For loop container
      image
      Things to remember For Each Loop task
      This is iterative task so where ever you will find things to iterate over file of variable we
      are going to use for each loop task
      Reference link for For Each Loop Container Task

      No comments:

      Post a Comment