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.

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.

The list of distinct country names is.

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

SSIS Foreach Loop Item Enumerator Example

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

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

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

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

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.

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

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

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.

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

Next, click the Parameters button and assign the SSIS Foreach Loop Item Enumerator country 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.

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

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

Within the Flat File Destination Editor, go to the Mappings tab to 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.

Run the SSIS Foreach Loop Item Enumerator package.

Now you can see each file has respective country sales.