0

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.

MJoy
  • 1,349
  • 2
  • 9
  • 23
  • SSIS is Microsoft SQL Server. You are using Oledb which is not usually used with SQL Server. Why are you using VSTA? – jdweng Jun 26 '21 at 14:30
  • Did you try `da.Fill(dt, (object) Variables.resultSet);` as suggested here: https://stackoverflow.com/a/54985125 ? – rene Jun 26 '21 at 14:34
  • @rene I tried using `da.Fill(dt, (object) Variables.resultSet);` but it's getting me the same error – MJoy Jun 26 '21 at 14:50
  • @jdweng I'm using VS 2019 to create the project, I'm not sure if I fully followed your question – MJoy Jun 26 '21 at 14:50
  • @MJoy make sure that you didn't read any data from the resultset before using it, since ADO.Recordset objects can be consumed only once. – Hadi Jun 27 '21 at 11:52

0 Answers0