How to loop through a generic object in SSIS Scrip

问题:

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.

回答1:

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.

回答2:

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

回答3:

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;

回答4:

awesome man,

This is working perfectly fine..

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

回答5:

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