Get data from a full result set variable in to script task using C#

Question

I need to get data from my SQL Task to a DataTable object using a script task to generate an email. But when I try to fill the data using OLEDB Adapter fill task, it generates an error:

OleDbDataAdapter Internal error: invalid row set accessor: Ordinal=1 Status=UNSUPPORTEDCONVERSION

Screenshot

as above,

public void Main()
{
    // TODO: Add your code here
    DataTable dt = new DataTable();
    String message = "";
    OleDbDataAdapter adapter = new OleDbDataAdapter();

    if (Dts.Variables.Contains("onErrorList") == true)
    {  
        try
        {
            try
            {
                adapter.Fill(dt, Dts.Variables["onErrorList"].Value);
            } catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }

            foreach (DataRow row in dt.Rows)
            {
                message = message + "\n" + "Error Time : " + row["message_time"] + "\n" + "Execution Path : " + row["executionpath"] + "\n" + "Error : " + row["MESSAGE"];
            }
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }
    }

    message = Dts.Variables["executionMessage"].Value + "\n" + message;


    try {
        sendMail("umairr.ayra@gmail.com", "Error in  ETL ", message);
        Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (Exception e)
    {

        MessageBox.Show(e.Message, "Mail Sending Failed");
        Dts.TaskResult = (int)ScriptResults.Failure;
    }


}

#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
/// 
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

this line is where the error has been generated:

adapter.Fill(dt, Dts.Variables["onErrorList"].Value);

and SQL Code I used to get the values

    SELECT  message_time,CAST(execution_path AS NVARCHAR(100)) AS executionpath , MESSAGE
FROM   (
       SELECT  em.*
       FROM    SSISDB.catalog.event_messages AS em 
       WHERE   em.operation_id = (SELECT MAX(execution_id) FROM SSISDB.catalog.executions)
          -- AND event_name NOT LIKE '%Validate%'
       )q 
WHERE   event_name = 'OnError'
ORDER BY message_time DESC

Result set mapping to variable

variable type

Please help me on this.

Can you provide the SQL Command used in the SQL Task, and how you mapped the Result to the variable? if you provide some additional screenshots it will be more specific. — Yahfoufi, Mar 04 '19 at 09:38
Your SSIS variable must be of type OBJECT, and you can not access it using .value like this. You need to cast/convert the OBJECT SSIS variable type to a type you can use in your C# code. Depending on what you need to do with it would depend on the type of conversion you need to do — Brad, Mar 04 '19 at 14:16

score 2 · Accepted Answer · answered Mar 04 '19 at 14:20

2

I am doing this to import a result set from SSIS variable to a data table to use later in my code. This is how I am doing it.

Your SSIS variable must be Object Datatype. If not you need to use that first.

Then you use c# to do this to get the data and convert it to the adapter like below (instead of converting directly to an adapter like you are trying to do):

// import SSIS variable of object type
Object OBJDataTableValidFieldListFull = Dts.Variables["User::SSISVariableObject"].Value;

// create datatable variable and dataadpapter
DataTable dtValidFieldListFull = new DataTable();
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();

// fill datatable from variable passed from SSIS Object type
dataAdapter.Fill(dtValidFieldListFull, OBJDataTableValidFieldListFull);

answered Mar 04 '19 at 14:20

Brad

3,454
3
27
50

1

i tried this, but still the same error at same point generated. – umair Mar 05 '19 at 02:39
this is worked, when i'm worked on it from start. Thank you. – umair Mar 05 '19 at 09:10
This is weird, i was trying the OP approach and it worked!! Do you have any explanation why we should assign it to an object variable before using it in the adapter? i didn't find that mentioned while searching for this issue. – Yahfoufi Mar 05 '19 at 13:18
As long as it is an object type coming from SSIS and it is cast properly into the adapter, it should work, the way I posted above makes it easier to read/see what is happening (and if any issues/errors happen, it is easier to see where/what part is causing the issue) if doing a direct cast could be a couple issues with the error making it harder to debug – Brad Mar 05 '19 at 19:10

Hadi · Answer 2 · 2019-03-04T18:14:26.417

0

First of all, make sure that Dts.Variables["onErrorList"].Value contains a valid recordset and it is of type System.Object.

In addition, I think that OledbDataAdapter has some limitations while reading from SQL since not all data types are supported i.e. nvarchar(max)

Similar issue

OleDbDataAdapter internal error: invalid row set accessor. Status=UNSUPPORTEDCONVERSION

edited Mar 04 '19 at 18:14

answered Mar 04 '19 at 08:46

Hadi

36,233
13
65
124

Get data from a full result set variable in to script task using C#

2 Answers2

Linked