The SSIS Foreach Variable Enumerator is used to enumerate the data inside a Variable. You can use this enumerator to loop over the Object variable to get data from that object. For example, to iterate data coming from Arrays, Array Lists etc. In this article we will show you, How to iterate Array List using SSIS Foreach Variable Enumerator, and save data into SQL Server table with example.
Let me create an Empty table to hold the data coming from Array List.
-- ForEach Variable Enumerator in SSIS
USE [SQL Tutorial]
CREATE TABLE [dbo].[ForEach Variable](
[Wid] [int] IDENTITY(1,1) NOT NULL,
[Day] [varchar](50) NULL
And you can see the data inside the table as well.
SSIS Foreach Variable Enumerator Example
In this example, we will enumerate array list, and copy the list items into database table. In order to do so, First Drag and drop the Script Task from SSIS toolbox into the Control Flow region as shown below
Before we start configuring the Script Task, let me create a variable of Type Object. This is the variable that we are going to use 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. We already explained the Script task configuration steps in our previous article so please refer the same. 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 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();
Dts.Variables["User::DayName"].Value = arr;
Now, Drag and drop the Foreach Loop Container into the Control Flow region as shown below
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. From the below screenshot you can observe that, we are selecting Foreach From Variable Enumerator because we want to loop over the variable.
As you can see from the below screenshot, we are selecting the DayName variable.
Next, we are assigning the returned value to string variable ( Day). This is very useful, if our Object variable is holding multiple column values.
Next, Drag and drop the Execute SQL Task from toolbox to Foreach loop container. This task will help us to insert the values into our Empty table.
Double click on it will open the Execute SQL Task Editor window. From the below screenshot you can observe that, we selected OLE DB as Connection type and selected the existing connection which is pointing to [SQL Tutorials] Database.
TIP: Please refer the OLE DB Connection manager article to understand the steps involved in creating new OLE DB Connection.
Next, we are writing the following SQL Query.
INSERT INTO [dbo].[ForEach Variable]
Here the question mark will be replaced by the parameter value.
Go to parameter mapping and select the parameter that we assigned in Foreach Loop container.
Click OK to finish configuring the SSIS Foreach Variable Enumerator package. Let us Run the package
Let us open the SQL Server management studio, and check whether we inserted the Array List items into the destination table using Foreach Variable Enumerator or not
Thank You for Visiting Our Blog