SSIS Foreach Loop Item Enumerator

The Foreach loop Container Item enumerator iterate over the items mentioned within the container columns. This SSIS Integration Services article shows the step in configuring the SSIS Foreach loop Item enumerator with an example.

To demonstrate this SSIS Foreach Loop Item Enumerator, we use the below query. Our job is to export this table data in different text files based on the country name. For instance, English Country Name = Australia will load into the Australia Sales CSV file.

SQL Tables Data

The list of distinct country names is.

Countries List

CSV files to load data. As you can see, all of them are empty.

Empty CSV Files

SSIS Foreach Loop Item Enumerator Example

Drag and drop the SSIS Foreach loop container into the control flow region to work with Item Enumerator.

SSIS ForEach Loop Item Enumerator 4

Double-Click on it will open the editor. Change the SSIS Enumerator type to Foreach Item Enumerator.

Change Type to SSIS Foreach Item Enumerator

Click the Columns button will open the For each Item Columns window. The add button helps to add a Column and change the Data type.

For the remaining LoopIetartions, use the below links.

  1. ADO Enumerator
  2. ADO.NET Schema Rowset Enum
  3. File Enumerator
  4. NodeList Enumerator
  5. SMO Enumerator
  6. Variable Enumerator
For each Item Columns window

Let me add those Country Names as the Column values or Items to iterate.

Add Countries to Column Values

Under the SSIS Foreach Loop Item Enumerator Variable mappings tab, Click the <New Variable>.

SSIS ForEach Loop Item Enumerator Variable Mapping Tab

If you already have one, select the same. Otherwise, use the below to create a variable of type string to hold the country names.

Create a new variable

Add data Flow Task to SSIS Foreach loop Item Enumerator container.

Add Data Flow Task to SSIS ForEach Loop Item Enumerator

Next, we use the below query with the WHERE condition. Here, ? represent the Parameter value.

SQL Query with Where Condition Parameter

Double-click to open the SSIS Data Flow Region. Then, drag the OLE DB Source and double-click on it to open the Editor. Next, click the New button to configure the OLE DB Connection Manager. Here, we have chosen the existing one, but you can create a new connection by clicking the New button.

Use OLEDB Source configure the OLE DB Connection Manager

Change the Data Access Mode option to SQL Command and write the query.

SELECT [EnglishCountryRegionName]
FROM [DimGeography] INNER JOIN DimCustomer ON 
DimGeography.GeographyKey = DimCustomer.GeographyKey
INNER JOIN [FactInternetSales] AS Fact ON
DimCustomer.CustomerKey = Fact.CustomerKey
WHERE EnglishCountryRegionName = ?
ORDER BY EnglishCountryRegionName
Sql Command

Next, click the Parameters button and assign the SSIS Foreach Loop Item Enumerator country variable.

Assign Parameter. to Variable

Drag the flat file destination and connect the OLE DB source to it. Next, Double-click on the flat file destination to open the Editor, and click on the new button to choose the Delimited as the flat file format.

Add Flat File Destination for SSIS ForEach Loop Item Enumerator

Click on the Browse button to select any one file to configure the basic things.

Select the File

Next, checkmark the Column Names in the first data row option to copy the headers.

Checkmark the Column Names in the first data row

Within the Flat File Destination Editor, go to the Mappings tab to check the input and available destination column mapping.

Check the input and available destination column mapping

Connection String properties

Select the Flat File Connection manager and Go to the Properties window. Next, click the browse button beside the Expressions.

Flat File Connection manager Properties

Choose the ConnectionString as the Property Click the expression and write the expression. Here, we copied the actual folder path + Country variable + Sales.csv. Integration Services throw an error for a single forward slash. So, use an escape character.

Write Expression for Flat File ConnectionString

Run the SSIS Foreach Loop Item Enumerator package.

Run the SSIS Foreach Loop Item Enumerator package

Now you can see each file has respective country sales.

SSIS Foreach Loop Item Enumerator Result

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.