Can a array list of C# be used to populate SSIS object variable?

Question

I have populated a list in C# script and assigned its value to SSIS object variable.

Then I used that object variable to execute some SQL query by looping through For each do enumerator.

I tried doing this by Foreach ado enumerator but getting error

X variable doesn't contain a valid data object.

Can anybody provide any inputs.

Answer 1

Youre using a list. Not a recordset and therefore you need to enumerate over a variable.

If you want to use ADO Recordset, you need to fill a datatable instead.

1. This shows you how to write to object with a variable list

2. This shows you how to write to object with recordset (using multiple values)

Like this:

1 .C# Script code - Write to Object with list using variable enumerator

public void Main()
    {
        // TODO: Add your code here

        List<string> NewList = new List<string>();

        NewList.Add("Ost");
        NewList.Add("Hest");

        Dts.Variables["User::NameList"].Value = NewList;

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

1. Variable settings in ssis

1. Foreach loop container settings

Use Foreach Variable Enumerator and use your object variable

Map your outcome to a variable(s)

1. Execute SQL Task test case

Write your SQL with variables

Map your variable to Parameter mapping

1. Result

2. C# Script code - Write to object with datatable using ADO enumerator

   public void Main()
    {
        // TODO: Add your code here

        DataTable dt = new DataTable();

        dt.Columns.Add("FilmName",typeof(string));
        dt.Columns.Add("ActorName",typeof(string));

        dt.Rows.Add("Starwars", "Harrison ford");
        dt.Rows.Add("Pulp fiction", "Samuel Jackson");


        Dts.Variables["User::NameList"].Value = dt;

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

2. Variable settings in ssis

2. Foreach loop container settings

Use Foreach ADO Enumerator and your object as variable

Map your outcome to variable(s)

2. Execute sql task test case

Write your SQL with variables

Map your variable(s) to Parameter mapping

2. Result