SSIS - For Each Loop Container, Object and Variables

This blog post is based on my learning from my colleague on using For Each loop container. I also had issues in converting object variables to string / int variables. We will be also looking into it.

Scenario - Do Some task for every entry in a table using For Each Loop Container. Example also covers assigning objects to variables

For Reading Object variables i referred to post SSIS - Reading object variable in Script task

Getting Started

Step 1 - Table A with few columns

CREATE TABLE [dbo].[TableB] (
  [Id] [int] NOT NULL,
  [Stage] [int] NULL,
  [Comments] [varchar] (500) NOT NULL,
)

Step 2 Insert Records into table

/****** Object: [dbo].[TableB] (6 rows scripted) ******/

INSERT INTO [dbo].[TableB] ([Id], [Stage], [Comments])

VALUES (1, 1, 'A Comments');

INSERT INTO [dbo].[TableB] ([Id], [Stage], [Comments])

VALUES (1, 2, 'A Second Comments');

INSERT INTO [dbo].[TableB] ([Id], [Stage], [Comments])

VALUES (1, 5, 'A 3rd Comments');

INSERT INTO [dbo].[TableB] ([Id], [Stage], [Comments])

VALUES (2, 1, 'B 1st Comments');

INSERT INTO [dbo].[TableB] ([Id], [Stage], [Comments])

VALUES (2, 9, 'B 2nd Comments');

INSERT INTO [dbo].[TableB] ([Id], [Stage], [Comments])

VALUES (2, 10, 'B 3rd Comments');

Step 3 - For Each Loop Container

Overall package structure looks as below. Fetch entries from table and loop thru and display results.

Step 3.1 - SSIS Package Variables

Step 3.2 - SQL Task Properties

Step 3.3 - SQL Task Return Data Set

Step 3.4 - For Each Loop Properties

Step 3.5 - Iterate and Print Entries in Object

Script Task Inside For Loop Container

C# code in the script task

     int id, stage;

     string comment;
     MessageBox.Show("Inside For Each Loop Container ");
       comment = Dts.Variables["User::Comment"].Value.ToString();
      id = Convert.ToInt32(Dts.Variables["User::Id"].Value.ToString());
     stage = Convert.ToInt32(Dts.Variables["User::Stage"].Value.ToString());
      MessageBox.Show("Id is " + id.ToString() + " Stage is " + stage.ToString() + " Comment is " + comment);


Step 4 - Iterate Object List Script Task - Only Read-Write variable passed in this step is User::ObjectList

For script to iterate the elements.

Add name space -

using System.Data.OleDb;

Paste below piece of code (Source - Reading object variable in Script task)

            // TODO: Add your code here
            int id, stage;
            string comment;
            // TODO: Add your code here
            OleDbDataAdapter oleDA = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            DataColumn col = null;
            DataRow row = null;
            MessageBox.Show("Inside Object List");
             oleDA.Fill(dt, Dts.Variables["User::ObjectList"].Value);
            foreach (DataRow row_ in dt.Rows)
            {
                row = row_;
                id = Convert.ToInt32(row["Id"].ToString());
                stage = Convert.ToInt32(row["Stage"].ToString());
                comment = row["Comments"].ToString();
                MessageBox.Show ("Id is " + id.ToString() + " Stage is " + stage.ToString() + " Comment is " + comment);
            }

Happy Learning!!!