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