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 example.

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 1

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 SSIS toolbox into the Control Flow region as shown below

SSIS ForEach Variable Enumerator 2

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.

SSIS ForEach Variable Enumerator 3

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

SSIS ForEach Variable Enumerator 4

Here, we first declared an Array List then we added the day names to that list. 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;
SSIS ForEach Variable Enumerator 5

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

SSIS ForEach Variable Enumerator 6

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

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.

SSIS ForEach Variable Enumerator 7

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

SSIS ForEach Variable Enumerator 8

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

SSIS ForEach Variable Enumerator 9

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 SQL Empty table.

SSIS ForEach Variable Enumerator 10

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 [SQL Tutorials] Database.

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

SSIS ForEach Variable Enumerator 11

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

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

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

SSIS ForEach Variable Enumerator 12

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

SSIS ForEach Variable Enumerator 13

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

SSIS ForEach Variable Enumerator 14

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

SSIS ForEach Variable Enumerator 15