SSIS ForEach Variable Enumerator

The SSIS Foreach Variable Enumerator is used to enumerate the data inside a Variable. Use this enumerator to loop over the Object variable to get data from that object. For example, to iterate data coming from Arrays and Array Lists. This article shows, How to iterate Array List using SSIS Foreach Variable Enumerator and save data into the SQL Server table with examples.

Let us create an Empty table to hold the data coming from an Array List.

-- ForEach Variable Enumerator in SSIS
USE [SQL Tutorial]
GO

CREATE TABLE [dbo].[ForEach Variable](
 [Wid] [int] IDENTITY(1,1) NOT NULL,
 [Day] [varchar](50) NULL
) 
GO

And you can see the data inside the table as well.

SSIS Foreach Variable Enumerator Example

In this SSIS ForEach Variable Enumerator example, we will enumerate the array list and copy the list items into a database table.

To do so, first, Drag and drop the Script Task from the SSIS toolbox into the Control Flow region, as shown below

Before we start configuring the Script Task, let us create a variable of type Object, and we use it inside the script to hold the array list items.

Double-click on the Script task will open the following editor to configure the Script task components. Here, we selected the previously created variable as Read and Write variable.

Next, please click on the Edit Script.. button to write the actual C# or VB Script

Here, we first declared an Array List and then added the day names. Lastly, we are assigning that array list to the DayName variable (Object Type)

C# code we used in the below screenshot is:

// ForEach Variable Enumerator in SSIS Example
ArrayList arr = new ArrayList();
          arr.Add("Sunday");
          arr.Add("Monday");
          arr.Add("Tuesday");
          arr.Add("Wednesday");
          arr.Add("Thusday");
          arr.Add("Friday");
          arr.Add("Saturday");

Dts.Variables["User::DayName"].Value = arr;

Now, Drag and drop the Foreach Loop Container into the SSIS Control Flow region

Double-click on it will open the Foreach Loop Editor to configure it. Within the General tab, Please specify valid and more meaningful Names and Descriptions.

Please go to the Collections tab to select the Enumerator. Here, we are selecting Foreach From Variable Enumerator because we want to loop over the variable.

From the below screenshot, we are selecting the DayName variable.

Next, we assign the returned value to the string variable ( Day). It is useful if our Object variable holds many column values.

Next, Drag and drop the Execute SQL Task from the toolbox to the Foreach loop container. This SSIS ForEach Variable Enumerator task helps us to insert the values into our Empty table.

Double-click on it will open the Execute SQL Task Editor window. Here, we selected OLE DB as the Connection type and selected the existing connection, which is pointing to the Database.

TIP: Please refer to the OLE DB Connection Manager article to understand creating a new OLE DB Connection.

Next, we are writing the following Query for SSIS ForEach Variable Enumerator.

INSERT INTO [dbo].[ForEach Variable]
           ([Day])
 VALUES (?)

Here the question mark will be replaced by the parameter value.

Go to parameter mapping and select the parameter that we assigned in the SSIS Foreach Loop enumerator.

Click OK to finish configuring the SSIS Foreach Variable Enumerator package. Let us Run the package

Let us open the SQL Server management studio to see whether we inserted the Array List items using SSIS Foreach Variable Enumerator or not