How to loop through a generic object in SSIS Script Task

Question

I have a generic object that is passed into a script task from a SQL Process. The object will essentially be a data table but in order to get the full result set from the sql process i have to store it in a generic object.

So If i have:

Object A = Dts.Variables[0];

How then would I go about extracting and then manipulating its values.

Baseically what i want to do is:

Object A = Dts.Variables[0];
strin x = A.Column[0].value.tostring();

But this obviously won't work.

Answer 1

I could not get any of the above answers to work, so listed below is the code I used to load the datatable. "User::transactionalRepDBs" is a SSIS variable of Object (System.Object) that was loaded through a "Full result set" from a execute SQL task script. The script task used is C#. This link assisted me.

using System.Data.OleDb;

DataTable dt= new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.Fill(dt, Dts.Variables["User::transactionalRepDBs"].Value);
String _showMe;

foreach (DataRow row in dt.Rows)
{
   //insert what you want to do here
           for (int i = 0, _showMe = ""; i < row.ItemArray.Length; i++ )
           {
               _showMe += row.ItemArray[i].ToString() + " | ";
           }

           MessageBox.Show("Data row #" + dt.Rows.IndexOf(row).ToString() + " value: " + _showMe);
}

Answer 2

There's nothing wrong with parsing a data table from an Object. I've seen Andy Leonard do it on his ETL frameworks.

You were on the correct path but you weren't seeing the whole picture. This code assigns the an object of type Variable (approximately) to A. You are then attempting to access a property that doesn't exist.

Object A = Dts.Variables[0];

Your need to grab the value of the variable. You can either do it as the assignment to A

Object A = Dts.Variables[0].Value;

Or if you needed to do something else with the actual variable, you'd keep your current code assignment of A then access the Value property.

Object A = Dts.Variables[0];
DataTable B = (DataTable) A.Value;
DataRow C = B.Row[0];
string x = C.Column[0].ToString();

The above code for datatable/datarow is approximate. The important take away is to access the goodies an SSIS variable is holding, you need to access the Value of the object.