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 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
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 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;
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 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.
From the below screenshot, we are selecting the DayName variable.
Next, we are assigning the returned value to the string variable ( Day). It is useful if our Object variable is holding 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 [SQL Tutorials] 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 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.
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 that we inserted the Array List items using SSIS Foreach Variable Enumerator or not