0

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159
Ishan Trikha
  • 251
  • 5
  • 6
  • 13

3 Answers3

4

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

}
ElegantFellow
  • 599
  • 2
  • 5
  • 10
2

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.

Jon Jaussi
  • 1,298
  • 3
  • 18
  • 36
  • To answer your question, one might need to use an object variable instead of a dataflow because one might be executing a dynamic query and not know, at design time, what columns will result from the query. – Tab Alleman Aug 25 '15 at 14:19
  • Because i may want to send an email and in the email i want the items from my variable listed out. – Shaheen K May 16 '17 at 15:39
2

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: enter image description here

enter image description here

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

enter image description here

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;
    }
Yasskier
  • 791
  • 1
  • 14
  • 36