CODEWITHSUNDEEP
ssisscript-taskforeach-loop-container
vani
vani

Reputation: 13

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

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 :

enter image description here

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

enter image description here

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 ?

Upvotes: 0

Views: 548

Answers (1)

Bitten Fleax
Bitten Fleax

Reputation: 272

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: enter image description here

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

  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.

Upvotes: 0

Related Questions