Reading object variable values in SSIS script component source

Question

Is it possible to read object variable values in SSIS script component source?

I have a variable, of type Object, which contains records from table populated by using a SQL Script Task.

I have used this Script Task and it's working perfectly by using below code

oleDA.Fill(dt, Dts.Variables("vTableRowsObj").Value) 

in this way where vTableRowsObj is object variable .

I want to read this object in an SSIS script component so that I can directly give the output from script component to the destination table.

The end goal is that I am planning to create more object variables and simply by reading these objects, give the output to destination tables from script component.

Answer 1

Given that you have a table with records populated by a SQL Script task, why is it necessary to load that data into a variable of type Object? Why not just use that table as a data source in a data flow? The basic steps are...

1) Run your SQL Script task and load your results to a table (sounds like you are already doing this)

2) Skip loading the records to the Object variable

3) Instead add a Data Flow Component as a downstream connection to your SQL Script Task

4) Add a Source component to your Data Flow: use the the table you populated with the SQL Script Task as your data source

5) Add a Destination component to your Data Flow: use your destination table as your data destination

However in the spirit of answering the question you asked directly (if I have in fact understood your question correctly), then the simple answer is yes you can use an SSIS script component as a data source in a data flow. This article walks you through the steps.

Answer 2

I had a similar issue.
Here's some links to reference and my code is below for simple output for ID and Name.

http://agilebi.com/jwelch/2007/03/22/writing-a-resultset-to-a-flat-file/
http://www.ssistalk.com/2007/04/04/ssis-using-a-script-component-as-a-source/
http://consultingblogs.emc.com/jamiethomson/archive/2006/01/04/SSIS_3A00_-Recordsets-instead-of-raw-files.aspx

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Xml;
using System.Data.OleDb;

public override void CreateNewOutputRows()
{
    DataTable dt = new DataTable();
    OleDbDataAdapter oleda = new OleDbDataAdapter();
    oleda.Fill(dt, this.Variables.ObjectVariable);

    foreach (DataRow row in dt.Rows)
    {
        Output0Buffer.AddRow();
        Output0Buffer.ID = Convert.ToInt32(row.ItemArray[0]);
        Output0Buffer.Name = row.ItemArray[1].ToString();
    }

}

Answer 3

Since I've stumbled on this problem today let me give you my solution:

First (something that you've done but placed here for clarity):

Create the ExecuteSQL task with "ResultSet" set to "Full result set" and assign it to the object type variable:

Then link it to the "Script task" and then add the variable either to "ReadOnly" or "ReadWriteVariables"

Now you just need to access this variable - as you suggested by filling it to a datatable, and then assign it to a string variable:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;   

public void Main()
    {
        // TODO: Add your code here            
        DataTable dt = new DataTable();
        var oleDa = new OleDbDataAdapter();
        oleDa.Fill(dt, Dts.Variables["Destination"].Value);
        string yourValueAsString= dt.Rows[0][0].ToString();
        Dts.Variables["MyStringVariable"].Value = yourValueAsString;                     
        [...]
        Dts.TaskResult = (int)ScriptResults.Success;
    }