How to Skip a Shared Object in SSIS Script Task

Question

I have a generic object that is passed to a script task from an SQL process. The object will essentially be a data table, but in order to get the full set of results from the sql process, I have to store it in a shared object.

So, if I have:

Object A = Dts.Variables[0]; 

How then am I going to extract and then manipulate its values.

Basically what I want to do:

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

But that obviously won't work.

Answer 1

There is nothing wrong with parsing a data table from an object. I saw Andy Leonard to do this on my ETL platforms.

You were on the right track, but you did not see the whole picture. This code sets an object of type Variable (approximately) to A. Then you try to access a property that does not exist.

 Object A = Dts.Variables[0]; 

Your need to capture the value of a variable. You can do it as assignment A

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

Or, if you need to do something else with the actual variable, you save your current assignment of code A, and 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. An important care is to access the pluses that are stored in the SSIS variable, you need to access the value of the object.

Answer 2

I could not get any of the above answers to work, so the ones listed below is the code I used to download the datatable. "User :: transactionalRepDBs" is the SSIS variable of the Object (System.Object) that was loaded through the "Complete Result Set" from the SQL script execution task. The task of the script is C #. This link helped 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 3

Proposition # 1: Access to variables by name, not a numeric index.

Proposition # 2: pass the result of the Value property to the type of the expected object.

In this way:

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

Answer 4

amazing person

It works great.

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

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