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:
- Looping through all or some of the files in a
folder
- 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:
- Initialize variables with list of array
values using SSIS
- 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
- 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:
- 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.
- 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.
- 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:
- 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.
- Useful to loop through all
tables/schemas/views/objects in a database
- 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
- 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