SSIS Foreach Loop Item Enumerator

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

We use the query below to demonstrate this SSIS Foreach Loop Item Enumerator. It returns the Fact Internet Sales information for all the available countries. We 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.

SELECT [EnglishCountryRegionName],[StateProvinceName],[City],[PostalCode]
   ,Fact.[UnitPrice],Fact.[ProductStandardCost],Fact.[TotalProductCost]
   ,Fact.OrderQuantity,Fact.[SalesAmount],Fact.[TaxAmt] 
FROM [DimGeography] INNER JOIN DimCustomer ON 
DimGeography.GeographyKey = DimCustomer.GeographyKey
INNER JOIN [FactInternetSales] AS Fact ON DimCustomer.CustomerKey = Fact.CustomerKey
ORDER BY EnglishCountryRegionName

Please use the below query to see a list of distinct country names. They are Australia, Canada, France, Germany, the United Kingdom, and the United States.

SELECT DISTINCT [EnglishCountryRegionName]
FROM [DimGeography]

The list of 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 the 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. Next, Click the Columns button to 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 links below.

  1. ADO Enumerator
  2. ADO.NET Schema Rowset Enum
  3. File Enumerator
  4. NodeList Enumerator
  5. SMO Enumerator
  6. Variable Enumerator
Change Type to SSIS Foreach Item Enumerator and 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>. 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 and SSIS ForEach Loop Item Enumerator Variable Mapping Tab

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

Add Data Flow Task to SSIS ForEach Loop Item Enumerator

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. We have chosen the existing one here, but you can create a new connection by clicking the New button.

Use OLEDB Source configure the OLE DB Connection Manager

Next, we use the below query with the WHERE condition. Here, ? represent the Parameter value. First, change the Data Access Mode option to SQL Command and write the query.

SELECT [EnglishCountryRegionName],[StateProvinceName],[City],[PostalCode]
   ,Fact.[UnitPrice],Fact.[ProductStandardCost],Fact.[TotalProductCost]
   ,Fact.OrderQuantity,Fact.[SalesAmount],Fact.[TaxAmt] 
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 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.

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 throws an error for a single forward slash. So, use an escape character.

Within the Flat File Connection manager Properties - 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