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 ?

Answer 1

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:

1. Create a new Script task outside of the ForLoop:

2. Ensure that the SSIS variable you've created has ReadWite access in the script:

3. 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;
   }
   

4. 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.