How to use a SSIS Dataset inside Dataflow Script component for Source?

Question

I'm looking for a way to use my Dataset variable in Script component of SSIS data flow task, here is what I tried till now

My First Task is Execute SQL task (CData PostgreSQL Task)

This would run a Postgre query and store the result in a variable User::resultSet which is of the data type DataSet

My Second Task is Data Flow Task

In my second task I wish to use the dataset as my source. I tried to achieve this by using Script Component (Source)

My Script

    public override void CreateNewOutputRows()
    {
        DataTable dt = (DataSet)Variables.resultSet;


        // The DataTable is now ready to use! No more recordset quirks.
        foreach (DataRow dr in dt.Rows)
        {

            // Add a new output row for this transformed data.
            OrdersBuffer.AddRow();

            // Now populate the columns
            OrdersBuffer.id = int.Parse(dr["id"].ToString());
            OrdersBuffer.created = int.Parse(dr["created"].ToString());
            OrdersBuffer.modified = int.Parse(dr["modified"].ToString());
        }
    }

Issue

In my Script, I cant figure out a way to convert the dataset into a datatable,

I believe the problem line is DataTable dt = (DataSet)Variables.resultSet; in my script

I also tried DataTable dt = resultSet.Tables[0]; & DataTable dt = resultSet.Table[0]; but all them are throwing syntax error.

Any lead or guidance will be really appreciated.

Answer 1

Try this one

DataTable dt = (Variables.resultSet as DataSet).Tables[0];

May throw an exception if type cast is not possible.

Answer 2

I have not used the dataset variable type but I have dont this with an OBJECT type variable and MS SQL DB. If you can change to use object variable this should work for you (or maybe if you cant you can see how I am passing the OBJECT variable and update your code to access the variable in similar way.

First I run a SQL task that has a result set as output in SSIS to my OBJECT variable data type. Then I pass it to my script task like this:

Inside the C# Script task I have this below code to access the OBJECT variable and then covert it to a data table that I use later on

Where this variable is my object variable type in the SSIS Package: User::ObjDataToSaveToExportFile

// this gets the data object and sets ti to a data table
            OleDbDataAdapter A = new OleDbDataAdapter();
            System.Data.DataTable dt = new System.Data.DataTable();
            A.Fill(dt, Dts.Variables["User::ObjDataToSaveToExportFile"].Value);

            // for test data
            //DataTable sourceTable = GetTestData();
            DataTable sourceTable = dt;

Answer 3

You can use below approach of using DataAdapter to fill datatable. Reference Article

public override void CreateNewOutputRows()
    {
        // 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)
        {
             // Add a new output row for this transformed data.
            OrdersBuffer.AddRow();

            // Now populate the columns
            OrdersBuffer.id = int.Parse(dr["id"].ToString());
            OrdersBuffer.created = int.Parse(dr["created"].ToString());
            OrdersBuffer.modified = int.Parse(dr["modified"].ToString());
        }