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.

score 2 · Accepted Answer · answered Jun 30 '21 at 16:09

2

Try this one

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

May throw an exception if type cast is not possible.

answered Jun 30 '21 at 16:09

Ferdipux

5,116
1
19
33

Fantastic !! It workss I've been trying to figure this our for few days now.. Cheers & Thank you – MJoy Jun 30 '21 at 17:02
If I may ask get your advice on more issue - My resultSet always returns 1000 rows although it contains many more (I noticed this when I ran it inside Dataflow source & ADO emulator). Is there any limitations to the rows that can be stored in single dataset table – MJoy Jun 30 '21 at 17:13
@MJoy, you can raise this as separate question. – Venkataraman R Jul 01 '21 at 03:47

score 0 · Answer 2 · answered Jun 28 '21 at 12:35

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;

The resultset created by my CData PostGre SQL task is of the type Dataset (not an Object), which I need to either convert to data table directly or first convert to object & then to datatable.. there lies my problem — MJoy, Jun 28 '21 at 12:58

score 0 · Answer 3 · answered Jun 30 '21 at 16:47

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

I'm not able to use this since my resultSet variable is of the type dataset. Error message "Object is not an ADODB.RecordSet or an ADODB.Record." — MJoy, Jun 30 '21 at 17:08

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

3 Answers3