MSBI # 23 – SSIS # 10 – Control Flow Tasks # 5 – For Each Loop Container Task

Continuing from my last post on SSIS Control flow task in which we talk lot about For Loop Container:

MSBI # 22 – SSIS # 9 – Control Flow Tasks # 4 – For Loop Container Task « (B)usiness (I)ntelligence Mentalist

We are covering following points in this article 

  • Why For Each Loop Container Task is Use ?
  • How For Each Loop Container Task is look like ?
  • Features of For Each Loop Container Task
  • How to use For Each Loop Container Task ?
  • What is alternative For Each For each Loop Container Task ?
  • Demonstration of using For Each Loop Container Task ?
  • Various Properties For each For Each Loop Container Task ?
  • Reference link For each For Each Loop Container Task

Now lets go through each aspects of For Each Loop Container Task :

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 ContainerGeneral
    2. SSIS Foreach Loop Container – Collection
    3. SSIS Foreach Loop Container Variable Mappings

    image

    SSIS Foreach Loop ContainerGeneral

    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

      http://msdn.microsoft.com/en-us/library/ms141724.aspx

      http://msdn.microsoft.com/en-us/library/ms177187.aspx

      http://msdn.microsoft.com/en-us/library/ms187670.aspx

      http://msdn.microsoft.com/en-us/library/ms188528.aspx

      SSIS – Use “Foreach Loop Container” and pass a variable to a OLE DB Source in a Data Flow « Sherry’s BI Corner

      Programming Foreach Loop Container – Enumerating Excel Files | SQL Lion

      http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/61987/

      http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

      Thanks for visiting my blog !!

      Hope you have understood various aspect of For Each Loop Container Task and ready to use every aspects for same

      If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog

      If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

      Where todays links are

      Link Resource # 17 : August 16 – August 18 « Dactylonomy of Web Resource

      Leave a Reply

      Fill in your details below or click an icon to log in:

      WordPress.com Logo

      You are commenting using your WordPress.com account. Log Out / Change )

      Twitter picture

      You are commenting using your Twitter account. Log Out / Change )

      Facebook photo

      You are commenting using your Facebook account. Log Out / Change )

      Google+ photo

      You are commenting using your Google+ account. Log Out / Change )

      Connecting to %s

      Blog at WordPress.com.

      Up ↑

      %d bloggers like this: