Tuesday, August 15, 2017

Foreach Loop Container in SSIS

The Foreach Loop Container is the one of the most important part of SQL Server Integration Services in the Control Flow window because it runs a repeating control flow in a package. This is the most widely used container to do multiple tasks inside it. In a package, looping is enabled by using a Foreach enumerator. 


So, we have to understand different types of Foreach enumerators –
  1. Foreach ADO enumerator to enumerate rows in tables and specify the ADO object source variable and the enumerator mode. The variable must be of Object data type.For example, you can get the rows in an ADO recordset.
  2. Foreach ADO.NET Schema Rowset enumerator to enumerate the schema information about a data source and specify the connection to a database and the schema to enumerate. For example, you can enumerate and get a list of the tables in the AdventureWorks2014 SQL Server database.
  3. Foreach File enumerator to enumerate files in a folder. The enumerator can traverse subfolders means specify a folder and the files to enumerate, the format of the file name of the retrieved files, and whether to traverse subfolders. For example, you can read all the files that have the *.csv file name extension in the Windows folder and its subfolders.
  4. Foreach From Variable enumerator to enumerate the enumerable object that a specified variable contains means specify the variable that contains the objects to enumerate. 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.
  5. Foreach Item enumerator to enumerate items that are collections which defines the items in the Foreach Item collection, including columns and column data types. For example, you can enumerate the names of executables and working directories that an Execute Process task uses.
  6. Foreach Nodelist enumerator to enumerate the result set of an XML Path Language (XPath) expression and specify the source of the XML document and configure the XPath operation. For example, this expression enumerates and gets a list of all the authors in the classical period: /authors/author[@period='classical'].
  7. Foreach SMO enumerator to enumerate SQL Server Management Objects (SMO) objects that specify the connection to a database and the SMO objects to enumerate. For example, you can enumerate and get a list of the views in a SQL Server database.
  8. Foreach HDFS File Enumerator to enumerate HDFS files in the specified HDFS location and specify a folder and the files to enumerate, the format of the file name of the retrieved files, and whether to traverse subfolders.
  9. Foreach Azure Blob enumerator to enumerate blobs in a blob container in Azure Storage and specify the Azure blob container that containers blobs to be enumerated.
In the next post, we have to go through each of them with some examples.
References - Microsoft

                Popular Posts