SSIS: Object variable

The SSIS Object variable is a generic object, but I have never seen it used as anything other than a dataset — which is the default behavior that is accessible to you when you dump records into the SSIS object type variable — the first table in the dataset object will contain your records.

If you want to see the values inside the object in debug mode, you will need to cast it as something in order to see anything, for example, in a script task, if you cast the SSIS object variable to a dataset, you can then debug into the script to look at it’s content and structure. Similarly, the foreach enumerator is casting the object as a dataset and you access the first tables columns and you can debug and see row by row the values in the set.

DataSet ds = Dts.Variables["User::vObjectList"].Value as DataSet;
foreach (DataTable tbl in ds.Tables)
{
   foreach(DataRow row in tbl.Rows)
   {
       foreach (DataColumn column in tbl.Columns)
       {
            MessageBox.Show(row[column].ToString());
       }
   }
}

Some more examples;

Here are a couple of examples to demonstrate what the mysterious object should be cast to in order to further explore it in .NET.

ADO.NET (using a System.Data.DataSet):

DataSet ds = (DataSet)Dts.Variables["obj"].Value;
MessageBox.Show(ds.Tables[0].Rows.Count.ToString());

OLE DB:

System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();
DataTable dt = new DataTable();
da.Fill(dt, Dts.Variables["obj"].Value);
MessageBox.Show(dt.Rows.Count.ToString());

Add to favorites