使用 C# 从完整的结果集变量中获取数据到脚本任务中 [英] Get data from a full result set variable in to script task using C#

问题描述

我需要使用脚本任务将数据从我的 SQL 任务获取到 DataTable 对象以生成电子邮件.但是当我尝试使用 OLEDB Adapter 填充任务填充数据时，它会产生一个错误:

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 内部错误:行集访问器无效:Ordinal=1 Status=UNSUPPORTEDCONVERSION

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

截图

如上，

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("[e-mail]", "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); 

和我用来获取值的 SQL 代码

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

结果集映射到变量

变量类型

请帮我解决这个问题.

推荐答案

我这样做是为了将结果集从 SSIS 变量导入数据表，以便稍后在我的代码中使用.这就是我的做法.

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.

您的 SSIS 变量必须是对象数据类型.如果不是，您需要先使用它.

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

然后您使用 c# 来执行此操作以获取数据并将其转换为如下所示的适配器(而不是像您尝试那样直接转换为适配器):

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

这篇关于使用 C# 从完整的结果集变量中获取数据到脚本任务中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！