I'm looking for a way to use the Script Component (as Source) in SSIS dataflow task to output a table generated from a Object data type. Below is what I tried
My Script Component properties
My Script
public class ScriptMain : UserComponent
{
public override void CreateNewOutputRows()
{
/*
Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
*/
// Set up the DataAdapter to extract the data, and the DataTable object to capture those results
OleDbDataAdapter da = new OleDbDataAdapter();
DataTable dt = new DataTable();
// Extract the data from the object variable into the table
da.Fill(dt, Variables.resultSet);
// Since we know the column metadata at design time, we simply need to iterate over each row in
// the DataTable, creating a new row in our Data Flow buffer for each
foreach (DataRow dr in dt.Rows)
{
// Create a new, empty row in the output buffer
OutputDataBuffer.AddRow();
// Now populate the columns
OutputDataBuffer.tablename = Convert.ToString(dr["table_name"]);
OutputDataBuffer.colname = Convert.ToString(dr["column_name"]);
OutputDataBuffer.jsonattr = Convert.ToString(dr["json_attribute"]);
OutputDataBuffer.datatype = Convert.ToString(dr["data_type"]);
}
}
}
Issue I'm facing
When I ran the package I got the below error, I tried some of the fix I saw for related issues but none of them worked
I suspect the issue is with the below part of my script (why because, when I removed this and ran it didn't throw any error)
// Extract the data from the object variable into the table
da.Fill(dt, Variables.resultSet);
Any lead or guidance will be really appreciated.