How to add values to System.Object Variable inside a foreach loop container with script task in SSIS?

Question

I'm trying to add new values to a variable (System.Object) inside a foreach loop container that is using that variable. How can i do that ?

To understand my flow :

first script i'm adding a value to that variable and its working fine.

But inside the loop when i'm trying to add new values to that variable its not working :(

I've tried this 2 codes:

DataTable myDataTable = new DataTable("LISTA_CONTACTOS");
myDataTable.Columns.Add(new DataColumn("columnText", typeof(string)));
DataRow myDataRow = myDataTable.NewRow();
myDataRow["columnText"] = "1";
myDataTable.Rows.Add(myDataRow);
Dts.Variables["User::LISTA_CONTACTOS"].Value = myDataTable;
Dts.TaskResult = (int)ScriptResults.Success;

DataTable dataTable = (DataTable)Dts.Variables["LISTA_CONTACTOS"].Value;
dataTable.Columns.Add(new DataColumn("contact_id", typeof(string)));
DataRow newRow = dataTable.NewRow();
newRow["contact_id"] = "8535939";
dataTable.Rows.Add(newRow);
Dts.Variables["LISTA_CONTACTOS"].Value = dataTable;
Dts.TaskResult = (int)ScriptResults.Success;

Think its something like that ...

Can anyone help me ?

Is `LISTA_CONTACTOS` set as read only or a read-write variable? What variable is for the Foreach Loop Container enumerating over? — billinkc, Jan 14 '23 at 18:28

Bitten Fleax · Answer 1 · 2023-06-19T13:55:37.270

I've just achieved this. I did the following:

Initialize your new DataTable outside of your ForLoop and assign it to the variable. The following steps are:

Create a new Script task outside of the ForLoop:
Ensure that the SSIS variable you've created has ReadWite access in the script:

Inside the Script, initialize your new DataTable. Add the columns you need. Then set your SSIS variable to the DataTable variable:

public voide Main()
{
    DataTable tbl = new DataTable();

    tbl.Columns.Add("ID");
    tbl.Columns.Add("Column1");
    tbl.Columns.Add("Column2");
    tbl.Columns.Add("Column3");

    tbl.PrimaryKey = new DataColumn[] { tbl.Columns["ID"] };

    Dts.Variables["User::AllRecordsTable"].Value = tbl;

    Dts.TaskResult = (int)ScriptResults.Success;
}

Inside your ForLoop, create a new Script. Ensure that the variable has ReadWrite access (like in step 2). In the code, you can then append rows onto your data table:

public override void PostExecute()
{
    var recordDataTable = (DataTable) Variables.AllRecordsTable;
    var rowCount = recordDataTable.Rows.Count;

    recordDataTable.Rows.Add(
        rowCount + 1,
        "ColumnValue1",
        "ColumnValue2",
        "ColumnValue3",
    );
}

FYI: In step 3, I used a Script Task inside the Control Flow. In step 4, I used a Script Component inside a Data Flow. That explains the difference in function names and the way we access SSIS variables.

How to add values to System.Object Variable inside a foreach loop container with script task in SSIS?

1 Answers1