SSIS ForEach Variable Enumerator

The SSIS Foreach Variable Enumerator enumerates 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 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 the 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 the 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 declared an Array List and added the day names. Lastly, we are assigning that array list to the DayName variable (Object Type)

C# code we used in the screenshot below 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 meaningful Names and Descriptions.

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

Reminig Iterations are:

SSIS ForEach Variable Enumerator 7

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

SSIS ForEach Variable Enumerator 8

Next, we assign the returned value to the string variable ( Day). It is useful if our Object variable holds 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 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 the Database.

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

SSIS ForEach Variable Enumerator 11

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.

SSIS ForEach Variable Enumerator 12

Go to parameter mapping and select the parameter 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 whether we inserted the Array List items using SSIS Foreach Variable Enumerator or not

SSIS ForEach Variable Enumerator 15