c# - 对象类型的SSIS变量到字符串的转换

Question

我正在使用 SQL Server 2014 和 Sisual studio 2013 的 SSIS 设计器 SSDT BI。我在执行 SQL 任务中有以下查询，单个结果集将值分配给字符串数据类型的变量“角色”。

SELECT DISTINCT (STUFF((SELECT CAST(',' + SubTableUser.UserRole AS VARCHAR(MAX))
        FROM TableUser AS SubTableUser
        WHERE SubTableUser.UserID = TableUser.UserID
        FOR XML PATH('')), 1, 1, '')) AS UserRole
FROM TableUser

执行 SQL 任务错误:

值类型(__ComObject)只能转换为Object类型的变量。为变量“role”赋值时出错:“赋值给变量的值类型 (DBNull)“User::role”不同于当前变量类型(字符串)。变量在执行期间可能不会改变类型。变量类型是严格的，除了 Object 类型的变量。”。

我试图在脚本任务 String.Format("{0}", Dts.Variables["User::role"]) 中将对象变量值分配给字符串变量并得到此值“System.__ComObject”而不是实际值。

Answer 1

我知道这是一篇较旧的帖子，但像我自己一样，我相信遇到同样问题的其他人也会遇到它。问题在于 SSIS 将 VARCHAR(MAX) 或 NVARCHAR(MAX) 转换为 ComObject。并且没有办法将其转换/转换为其他任何东西。哈迪的上述建议是不正确的。由于它是 varchar(max)，因此无法将其转换/强制转换为任何内容，并且必须在结果中将其视为对象。

我的问题是我正在为远远超过 8000 条的电子邮件消息生成一个包含嵌套表格的复杂 HTML 正文，因此无法将我的代码限制为 VARCHAR(8000)。我还附上了一个包含更多详细信息的 Excel 文件。过去，我一直使用 sp_dbSendMail 来完成这项工作，我觉得这要容易得多，但是我们的 IT“领导”决定他们想限制我们启用它的能力，因此我们必须找到绕过它的方法，直到工作场所的政治问题得到解决。解决了。​​

我发现唯一可行的方法是返回一个表，而不是一个变量。在 SSIS 结果集中创建您的变量作为对象并将其结果名称指定为 0。然后将该结果名称分配给您创建的用户变量 (User::objResultSet)。然后在您的 VstaProject 中将对象 (User::objResultSet) 视为一个表并提取您的值。请记住，SSIS 和 Vsta 不喜欢我一直使用的下划线 (_)，因此请适本地命名您的列和变量。

这是我所做的。在您的执行 SQL 任务中，为您的 SQL 语句输入以下变体。此代码假定您已经收集并创建了放入表中的所有值(电子邮件正文、电子邮件主题等)。出于安全目的，我只将输出放入表中:

CREATE TABLE #EMAIL_TEMP
    (
    EmailBody       VARCHAR(MAX)
    ,EmailSubject   VARCHAR(255)
    ,EmailTo        VARCHAR(8000)
    ,EmailAttach    VARCHAR(8000)
    )

    INSERT INTO #EMAIL_TEMP
    (EmailBody, EmailSubject, EmailTo, EmailAttach)
    SELECT CAST(ISNULL(@tableHTML , '') AS VARCHAR(MAX))    AS EmailBody
        ,ISNULL(@emailSubject, '')                          AS EmailSubject
        ,ISNULL(@TO_LIST , '')                              AS EmailTo          
        ,ISNULL(@ATTACH, '')                                AS EmaiAttach
        
    SELECT EmailBody, EmailSubject, EmailTo, EmailAttach
    FROM #EMAIL_TEMP

您会注意到我可以将我的表数据类型保留为电子邮件正文的 VARCHAR(MAX)。可以对所有列执行此操作，因为您要将其视为一个对象。

现在在您的脚本任务中使用以下代码:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net;
using System.Net.Mail;
using System.Data.OleDb;

    public void Main()
        {

            OleDbDataAdapter A = new OleDbDataAdapter();
            System.Data.DataTable dt = new System.Data.DataTable();
            A.Fill(dt, Dts.Variables["User::objResultSet"].Value);
            
            string EmailBody = "";
            string EmailSubject = "";
            string EmailTo = "";
            string EmailFrom = "sombodycool@someplace.com";
            string EmailAttach = "";

            foreach (DataRow row in dt.Rows)
            {

                object[] array = row.ItemArray;
                EmailBody = array[0].ToString();
                EmailSubject = array[1].ToString();
                EmailTo = array[2].ToString();
                EmailAttach = array[3].ToString();
                
            }

            string htmlMessageFrom = EmailFrom.ToString();
            string htmlMessageTo = EmailTo.ToString();
            string htmlMessageSubject = EmailSubject.ToString();
            string htmlMessageBody = EmailBody.ToString();
            string htmlMessageAttach = EmailAttach.ToString();
            string smtpServer = Dts.Variables["HtmlEmailServer"].Value.ToString();

            SendMailMessage(htmlMessageFrom, htmlMessageTo, htmlMessageSubject, EmailBody, htmlMessageAttach, true, smtpServer);

            Dts.TaskResult = (int)ScriptResults.Success;

        }

        private void SendMailMessage(string From, string SendTo, string Subject, string Body, string Attach, bool IsBodyHtml, string Server)
        {

            MailMessage htmlMessage;
            SmtpClient mySmtpClient;

            htmlMessage = new MailMessage(From, SendTo, Subject, Body);
            htmlMessage.IsBodyHtml = IsBodyHtml;

            System.Net.Mail.Attachment attachment;
            attachment = new System.Net.Mail.Attachment(Attach);
            htmlMessage.Attachments.Add(attachment);

            mySmtpClient = new SmtpClient(Server);
            mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials;
            mySmtpClient.Send(htmlMessage);

        }

确保在关闭之前保存您的 Vsta 代码，否则您将丢失它。然后在您的脚本任务上选择确定并进行测试。

希望对大家有所帮助。我搜索并为此工作了一个多星期。我找到的文章都没有答案，只有尖刻的、不完整的或无效的回复。