CODEWITHSUNDEEP
c#ssis
some_bloody_fool
some_bloody_fool

Reputation: 4685

How to loop through a generic object in SSIS Script Task

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.

Upvotes: 6

Views: 32278

Answers (5)

user7709700
user7709700

Reputation: 11

         // Works Perfectly fine ....ssis , c#
             DataTable dt = new DataTable();
        OleDbDataAdapter adapter = new OleDbDataAdapter();
        adapter.Fill(dt, Dts.Variables["User::VariableObj"].Value);


        foreach (DataColumn cols in dt.Columns)
        {
            MessageBox.Show("Colum Name = " + cols.ToString());

        }


            foreach (DataRow row in dt.Rows)
            {

                MessageBox.Show( "rows ID =  " + row[0].ToString() + " rows       
                Name =  " + row[1].ToString());
              }

Upvotes: 0

Tequila
Tequila

Reputation: 864

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);
}

Upvotes: 13

Aswartha
Aswartha

Reputation: 66

awesome man,

This is working perfectly fine.. 

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

Upvotes: 1

billinkc
billinkc

Reputation: 61259

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.

Upvotes: 10

Edmund Schweppe
Edmund Schweppe

Reputation: 5132

Suggestion #1: Access variables by name, not numeric index.

Suggestion #2: Cast the result of the Value property to the type of object you're expecting.

Thus:

string myString = (string)Dts.Variables["MyString"].Value;
DataTable myTable = (DataTable)Dts.Variables["MyTable"].Value;
DataTable myOtherTable = Dts.Variables["MyOtherTable"].Value as DataTable;

Upvotes: 3

Related Questions