Foreach Loop Editor (Collection Page)

 

Applies To: SQL Server 2016

Use the Collection pageof the Foreach Loop Editor dialog box to specify the enumerator type and configure the enumerator.

To learn about the Foreach Loop container and how to configure it, see Foreach Loop Container and Configure a Foreach Loop Container.

Enumerator
Select the enumerator type from the list. This property has the options listed in the following table.

ValueDescription
Foreach File EnumeratorEnumerate files. Selecting this value displays the dynamic options in the section, Foreach File Enumerator.
Foreach Item EnumeratorEnumerate values in an item. Selecting this value displays the dynamic options in the section, Foreach Item Enumerator.
Foreach ADO EnumeratorEnumerate tables or rows in tables. Selecting this value displays the dynamic options in the section, Foreach ADO Enumerator.
Foreach ADO.NET Schema Rowset EnumeratorEnumerate a schema. Selecting this value displays the dynamic options in the section, Foreach ADO.NET Enumerator.
Foreach From Variable EnumeratorEnumerate the value in a variable. Selecting this value displays the dynamic options in the section, Foreach From Variable Enumerator.
Foreach Nodelist EnumeratorEnumerate nodes in an XML document. Selecting this value displays the dynamic options in the section, Foreach Nodelist Enumerator.
Foreach SMO EnumeratorEnumerate a SMO object. Selecting this value displays the dynamic options in the section, Foreach SMO Enumerator.
Foreach HDFS File EnumeratorEnumerate HDFS files in the specified HDFS location. Selecting this value displays the dynamic options in the section, Foreach HDFS File Enumerator.
Foreach Azure Blob EnumeratorEnumerate blob files in the specified blob location. Selecting this value displays the dynamic options in the section, Foreach Azure Blob Enumerator.

Expressions
Click or expand Expressions to view the list of existing property expressions. Click the ellipsis button (…) to add a property expression for an enumerator property, or edit and evaluate an existing property expression.

Related Topics:  Integration Services (SSIS) Expressions, Property Expressions Editor, Expression Builder

Enumerator = Foreach File Enumerator

You use the Foreach File enumerator to enumerate files in a folder. For example, if the Foreach Loop includes an Execute SQL task, you can use the Foreach File enumerator to enumerate files that contain SQL statements that the Execute SQL task runs. The enumerator can be configured to include subfolders.

The content of the folders and subfolders that the Foreach File enumerator enumerates might change while the loop is executing because external processes or tasks in the loop add, rename, or delete files while the loop is executing. This means that a number of unexpected situations may occur:

  • If files are deleted, one task in the Foreach Loop may perform work on a different set of files than the files used by subsequent tasks.

  • If files are renamed and an external process automatically adds files to replace the renamed files, the Foreach Loop might perform work twice on the same file content.

  • If files are added, it may be difficult to determine for which files the Foreach Loop performed work.

Folder
Provide the path of the root folder to enumerate.

Browse
Browse to locate the root folder.

Files
Specify the files to enumerate.

System_CAPS_ICON_note.jpg Note


Use wildcard characters (*) to specify the files to include in the collection. For example, to include files with names that contain “abc”, use the following filter: *abc*.

When you specify a file name extension, the enumerator also returns files that have the same extension with additional characters appended. (This is the same behavior as that of the dir command in the operating system, which also compares 8.3 file names for backward compatibility.) This behavior of the enumerator could cause unexpected results. For example, you want to enumerate only Excel 2003 files, and you specify "*.xls". However, the enumerator will also return Excel 2007 files because those files have the extension, ".xlsx".

You can use an expression to specify the files to include in a collection, by expanding Expressions on the Collection page, selecting the FileSpec property, and then clicking the ellipsis button (…) to add the property expression. For more information about dynamically selecting specified files, see SSIS–Dynamically set File Mask : FileSpec

Fully qualified
Select to retrieve the fully qualified path of file names. If wildcard characters are specified in the Files option, then the fully-qualified paths that are returned match the filter.

Name only
Select to retrieve only the file names. If wildcard characters are specified in the Files option, then the file names returned match the filter.

Name and extension
Select to retrieve the file names and their file name extensions. If wildcard characters are specified in the Files option, then the name and extension of files returned match the filter.

Traverse Subfolders
Select to include the subfolders in the enumeration.

Enumerator = Foreach Item Enumerator

You use the Foreach Item enumerator to enumerate items in a collection. You define the items in the collection by specifying columns and column values. The columns in a row define an item. For example, an item that specifies the executables that an Execute Process task runs and the working directory that the task uses has two columns, one that lists the names of executables and one that lists the working directory. The number of rows determines the number of times that the loop is repeated. If the table has 10 rows, the loop repeats 10 times.

To update the properties of the Execute Process task, you map variables to item columns by using the index of the column. The first column defined in the enumerator item has the index value 0, the second column 1, and so on. The variable values are updated with each repeat of the loop. The Executable and WorkingDirectory properties of the Execute Process task can then be updated by property expressions that use these variables.

Define the items in the For Each Item collection
Provide a value for each column in the table.

System_CAPS_ICON_note.jpg Note


A new row is automatically added to the table after you enter values in row columns.

System_CAPS_ICON_note.jpg Note


If the values provided are not compatible with the column data type, the text is colored red.

Column data type
Lists the data type of the active column.

Remove
Select an item, and then click Remove to remove it from the list.

Columns
Click to configure the data type of the columns in the item.

Related Topics: For Each Item Columns Dialog Box UI Reference

Enumerator = Foreach ADO Enumerator

You use the Foreach ADO enumerator to enumerate rows or tables in an ADO or ADO.NET object that is stored in a variable. For example, if the Foreach Loop includes a Script task that writes a dataset to a variable, you can use the Foreach ADO enumerator to enumerate the rows in the dataset. If the variable contains an ADO.NET dataset, the enumerator can be configured to enumerate rows in multiple tables or to enumerate tables.

ADO object source variable
Select a user-defined variable in the list, or click <New variable...> to create a new variable.

System_CAPS_ICON_note.jpg Note


The variable must have the Object data type, otherwise an error occurs.

Related Topics: Integration Services (SSIS) Variables, Add Variable

Rows in first table
Select to enumerate only rows in the first table.

Rows in all tables (ADO.NET dataset only)
Select to enumerate rows in all tables. This option is available only if the objects to enumerate are all members of the same ADO.NET dataset.

All tables (ADO.NET dataset only)
Select to enumerate tables only.

Enumerator = Foreach ADO.NET Schema Rowset Enumerator

You use the Foreach ADO.NET Schema Rowset enumerator to enumerate a schema for a specified data source. For example, if the Foreach Loop includes an Execute SQL task, you can use the Foreach ADO.NET Schema Rowset enumerator to enumerate schemas such as the columns in the AdventureWorks database, and the Execute SQL task to get the schema permissions.

Connection
Select an ADO.NET connection manager in the list, or click <New connection...> to create a new ADO.NET connection manager.

System_CAPS_ICON_important.jpg Important


The ADO.NET connection manager must use a .NET provider for OLE DB. If connecting to SQL Server, the recommended provider to use is the SQL Server Native Client, listed in the .Net Providers for OleDb section of the Connection Manager dialog box.

Related Topics: ADO Connection Manager, Configure ADO.NET Connection Manager

Schema
Select the schema to enumerate.

Set Restrictions
Set the restrictions to apply to the specified schema.

Related Topics: Schema Restrictions Dialog Box

Enumerator = Foreach From Variable Enumerator

You use the Foreach From Variable enumerator to enumerate the enumerable objects in the specified variable. For example, if the Foreach Loop includes an Execute SQL task that runs a query and stores the result in a variable, you can use the Foreach From Variable enumerator to enumerate the query results.

Variable
Select a variable in the list, or click <New variable...> to create a new variable.

Related Topics: Integration Services (SSIS) Variables, Add Variable

Enumerator = Foreach NodeList Enumerator

You use the Foreach Nodelist enumerator to enumerate the set of XML nodes that results from applying an XPath expression to an XML file. For example, if the Foreach Loop includes a Script task, you can use the Foreach NodeList enumerator to pass a value that meets the XPath expression criteria from the XML file to the Script task.

The XPath expression that applies to the XML file is the outer XPath operation, stored in the OuterXPathString property. If the XPath enumeration type is set to ElementCollection, the Foreach NodeList enumerator can apply an inner XPath expression, stored in the InnerXPathString property, to a collection of element.

To learn more about working with XML documents and data, see "Employing XML in the .NET Framework" in the MSDN Library.

DocumentSourceType
Select the source type of the XML document. This property has the options listed in the following table.

ValueDescription
Direct inputSet the source to an XML document.
File connectionSelect a file that contains the XML document.
VariableSet the source to a variable that contains the XML document.

DocumentSource
If DocumentSourceType is set to Direct input, provide the XML code, or click the ellipsis (…) button to provide XML by using the Document Source Editor dialog box.

If DocumentSourceType is set to File connection, select a File connection manager, or click <New connection...> to create a new connection manager.

Related Topics: File Connection Manager, File Connection Manager Editor

If DocumentSourceType is set to Variable, select an existing variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services (SSIS) Variables, Add Variable.

EnumerationType
Select an enumeration type from the list. This property has the options listed in the following table.

ValueDescription
NavigatorEnumerate using an XPathNavigator.
NodeEnumerate nodes returned by an XPath operation.
NodeTextEnumerate text nodes returned by an XPath operation.
ElementCollectionEnumerates element nodes returned by an XPath operation.

OuterXPathStringSourceType
Select the source type of the XPath string. This property has the options listed in the following table.

ValueDescription
Direct inputSet the source to an XML document.
File connectionSelect a file that contains the XML document.
VariableSet the source to a variable that contains the XML document.

OuterXPathString
If OuterXPathStringSourceType is set to Direct input, provide the XPath string.

If OuterXPathStringSourceType is set to File connection, select a File connection manager, or click <New connection...> to create a new connection manager.

Related Topics: File Connection Manager, File Connection Manager Editor

If OuterXPathStringSourceType is set to Variable, select an existing variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services (SSIS) Variables, Add Variable.

InnerElementType
If EnumerationType is set to ElementCollection, select the type of inner element in the list.

InnerXPathStringSourceType
Select the source type of the inner XPath string. This property has the options listed in the following table.

ValueDescription
Direct inputSet the source to an XML document.
File connectionSelect a file that contains the XML document.
VariableSet the source to a variable that contains the XML document.

InnerXPathString
If InnerXPathStringSourceType is set to Direct input, provide the XPath string.

If InnerXPathStringSourceType is set to File connection, select a File connection manager, or click <New connection...> to create a new connection manager.

Related Topics: File Connection Manager, File Connection Manager Editor

If InnerXPathStringSourceType is set to Variable, select an existing variable, or click <New variable...> to create a new variable.

Related Topics: Integration Services (SSIS) Variables, Add Variable.

Enumerator = Foreach SMO Enumerator

You use the Foreach SMO enumerator to enumerate SQL Server Management Object (SMO) objects. For example, if the Foreach Loop includes an Execute SQL task, you can use the Foreach SMO enumerator to enumerate the tables in the AdventureWorks database and run queries that counts the number of rows in each table.

Connection
Select an existing ADO.NET connection manager, or click <New connection...> to create a new connection manager.

Related Topics: ADO.NET Connection Manager, Configure ADO.NET Connection Manager

Enumerate
Specify the SMO object to enumerate.

Browse
Select the SMO enumeration.

Related Topics: Select SMO Enumeration Dialog Box

Enumerator = Foreach HDFS File Enumerator

The Foreach HDFS File Enumerator enables an SSIS package to enumerate HDFS files in the specified HDFS location. The name of each HDFS file can be stored in a variable and used in tasks inside the Foreach Loop Container.

Hadoop Connection Manager
Specify an existing Hadoop Connection Manager or create a new one, which points to where the HDFS files are hosted. For more info, see Hadoop Connection Manager.

Directory Path
Specify the name of the HDFS directory that contains the HDFS files to be enumerated.

File name filter
Specify a name filter to select files with a certain name pattern. For example, MySheet*.xls* will include files such as MySheet001.xls and MySheetABC.xlsx.

Retrieve file name
Specify the file name type retrieved by SSIS.

  • Fully qualified name means the full name which contains the directory path and file name.

  • Name only means only the file name is retrieved.

Traverse subfolders
Specify whether to loop through subfolders recursively.

On the Variable Mappings page of the editor, select or create a variable to store the name of the enumerated HDFS file.

Enumerator = Foreach Azure Blob Enumerator

The Azure Blob Enumerator enables an SSIS package to enumerate blob files in the specified blob location. The name of enumerated blob file can be stored in a variable and used in tasks inside the Foreach Loop Container.

The Azure Blob Enumerator is a component of the SQL Server Integration Services (SSIS) Feature Pack for Azure for SQL Server 2016. Download the Feature Pack here.

Azure storage connection manager
Select an existing Azure Storage Connection Manager or create a new one that refers to an Azure Storage Account.

Related Topics: Azure Storage Connection Manager.

Blob container name
Specify the name of the blob container that contains the blob files to be enumerated..

Blob directory
Specify the specify the blob directory that contains the blob files to be enumerated. The blob directory is a virtual hierarchical structure.

Blob name filter
Specify a name filter to enumerate files with a certain name pattern. E.g. MySheet*.xls* will include files such as MySheet001.xls and MySheetABC.xlsx.

Blob time range from/to filter
Specify a time range filter. Files modified after TimeRangeFrom and before TimeRangeTo will be enumerated.

Integration Services Error and Message Reference
Foreach Loop Editor (General Page)
Foreach Loop Editor (Variable Mappings Page)
Expressions Page
For Loop Container

Community Additions

ADD
Show: