Extract data from the Object into DataTable using Script Component in SSIS Data Flow Task

1.2k Views Asked by At

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

enter image description here

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

enter image description here

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.

0

There are 0 best solutions below