For each Loop Container in SSIS

Bandi
Posted by in Sql Server category on for Advance level | Points: 250 | Views : 1919 red flag

In this article, we are going to look into ForEach Loop Contrainer in SQL Server Integration Services. There are seven different types of Loops/enumerators under ForEach Loop Container..

Introduction

There are 7 different types of enumerators with Foreach Loop Container. ForEach Loop container is to enumerate through each item/file/row of source...



Foreach File Enumerator

File enumerator is to traverse through the all files and sub folders of a folder/directory.

Usage Scenario:

  1. Looping through all or some of the files in a folder
  2. Loading multiple excel/flat/CSV files


Foreach Item Enumerator

Item Enumerator is useful to loop through list of the items in Collection. Here the list is static at execution time.

Usage Scenario:

  1. Initialize variables with list of array values using SSIS
  2. Foreach Item enumerator is useful to pass on list of static values to the variables. It helps developer/user not to create database table just to pass static values for Foreach Loop
  3. This loop container is very useful to run same query/operations against different SQL Servers or databases

Foreach ADO Enumerator

ADO Enumerator is to loop through the rows in an existing table

Usage Scenario:

  1. To do user validations in the applications. For example, you have the user details in the database table and then validate the each user by using Foreach ADO enumerator; then process each record as per the requirement.
  2. File Name validations: suppose that file to be processed are in one database table and should not allow any other files except file names in a table. In this case we should have all file names in a table then use ADO enumerator to loop through each file and then process accordingly.
  3. To loop through all rows in a table or loop through all tables/result sets from SQL

Foreach ADO .NET Schema Rowset Enumerator

Loops through schema and metadata of a data source. It enumerate through schema information about a data source (supported by the OLEDB provider). When data source is SQL Server, the metadata will be fetched from INFORMATION SCHEMA views. For example, to get list of tables, schemas, column information, constraints etc from a database.

Usage Scenario:

  1. This enumerator is very useful to load all sheets in an excel file, whereas Foreach File enumerator loops through only each file in a folder.
  2. Useful to loop through all tables/schemas/views/objects in a database
  3. Export data of each table into a separate EXCEL/CSV file with name of same table name. here the output structure varies for each table
  4. Can apply filter on looping

Foreach From Variable Enumerator

Loops through the values of a variable, for example a DataTable where we loop around the rows.

We can replace this enumerator with ADO .NET enumerator. But it will be very useful when we consider performance. We can load arrays from .NET code without using data sets and this will save memory consumption, because arrays use much lower resource and memory than dataset. From Variable enumerator will be very helpful when you work on large data streams every night in your ETL scenario.

Scenario:

  • Any Text/CSV files coming from a source directory
  • A loop structure should only pick the files that does not exists in the Archive folder.
  • Load them onto the database table and then move to Archive folder
  • New file names can found by using Script task and then fill them into an ArrayList, the ArrayList will be used in the Foreach Loop

Foreach NodeList Enumerator

Enumerates/parses through the XML document/nodes/tags. Enumerate nodes in an XML document. Very useful to parse through the web service or DDL (Dynamic Description Logic) which is in XML file format.

Scenario:

  • List of teams that played in 2012 fetched from a web service
  • A loop structure required to loop through team names, and call another web service to get full details of each team based on another web service.
  • The second web service accepts team name as an input parameter
  • Results of inner web services should be written into xml files named based on each time, such as XYZ.xml

Foreach SMO Enumerator

Enumerate SQL Server Management Object (SMO) objects.

  • Looping through SQL Server jobs, Logins, Linked Servers
  • Assume that we have staging data in different servers ( ServerA & ServerB), then we needed to load that data to destination DataMart (DestinationServer) 
  • -->> create two linked servers be pointing to staging servers in the destination server
  • Loop structure required to loop through these linked servers (and any future linked servers with customer info), and load the data from tables into an integrated database

Conclusion
This article is focused to explain the usage of each enumerator available in the Foreach Loop Container of SSIS.

Page copy protected against web site content infringement by Copyscape

About the Author

Bandi
Full Name: Chandrika Bandi
Member Level: Platinum
Member Status: Member,MVP
Member Since: 7/23/2013 5:52:37 AM
Country: India
[B]Mark This Response as Answer[/B] -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif
http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)