Tuesday, March 15, 2011

Foreach Loop Enumerators

The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages but 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.

SSIS provides the following enumerator types:

  • Foreach File Enumerator to enumerate files in a folder. The enumerator can traverse subfolders. For example, you can read all the files that have the *.csv file name extension in the folder and its subfolders.
  • Foreach Item Enumerator to enumerate items that are collections. For example, you can enumerate the names of executables and working directories that an Execute Process task uses.
  • Foreach ADO Enumerator to enumerate rows in tables. For example, you can get the rows in an ADO recordset.
  • Foreach ADO.NET Schema Rowset Enumerator to enumerate the schema information about a data source. For example, you can enumerate and get a list of the tables of your SQL Server database.
  • Foreach From Variable Enumerator to enumerate the object that a specified variable contains. The enumerable object can be an array, an ADO.NET DataTable, an Integration Services enumerator, and so on. For example, you can enumerate the values of an array that contains the name of Servers.
  • Foreach Nodelist Enumerator to enumerate the result set of an XML Path Language (XPath) expression. For example, this expression enumerates and gets a list of all the products of Electronics type: /products/product[@type='Electronics'].
  • Foreach SMO Enumerator to enumerate SQL Server Management Objects (SMO) objects. For example, you can enumerate and get a list of the views in a SQL Server database.

Here are few FREE resources you may find helpful.