SSIS variable of object type to string conversion

Question

I am using SQL Server 2014 and for SSIS designer SSDT BI with Sisual studio 2013. I have the following query in an Execute SQL Task with Single resultset assigning the value to variable "role" of string data type.

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

Execute SQL Task Error:

The value type (__ComObject) can only be converted to variables of type Object. An error occurred while assigning a value to variable "role": "The type of the value (DBNull) being assigned to variable "User::role" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object.".

I tried to assign the object variable value to a string variable in a script task String.Format("{0}", Dts.Variables["User::role"]) and get this value "System.__ComObject" instead of the actual value.

Answer 1

I know this is an older post, but like myself, I'm sure somebody else with the same issue will run into it. The issue is that SSIS converts VARCHAR(MAX) or NVARCHAR(MAX) into an ComObject. And there is no way to cast/convert it to anything else. The suggestion Hadi made above is incorrect. Since it's varchar(max) it cannot be converted/casted to anything and must be treated as an object in the result.

My issue was I'm generating an elaborate HTML body with nested tables for my email message that far exceeds 8000, so restricting my code to VARCHAR(8000) was not an option. I'm also attaching an Excel file with more detail. In the past I've always done this using sp_dbSendMail, which I feel is much easier, but our IT "leaders" decided they want to restrict our ability to enable it, so we had to find a way around it until the workplace politics is ironed out.

The only way around it that I have found that works is to return a Table, not a variable. Create your variable within SSIS Result Set as an Object and give it a Result Name of 0. Then assign that Result Name to the user Variable you created (User::objResultSet). Then in your VstaProject treat the object (User::objResultSet) as a table and extract out your value(s). Keep in mind SSIS and Vsta do NOT like underscores (_), which I use all the time, so name your columns and variables appropriately.

Here's what I've done. In your Execute SQL Task, enter a variation below for your SQL Statement. This code assumes you have already gathered and created all of the values being put into the table (Email Body, Email Subject etc). For security purposes I'm only putting in the output to the table:

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

You'll notice that I'm able to keep my table datatype as VARCHAR(MAX) for the email body. This can be done for all of the columns since you're going to be treating it as an object.

Now in your Script Task using the following code:

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

        }

Make sure to save your Vsta code before you close it, or you will lose it. Then select Ok on your Script Task and test it.

I hope this helps somebody. I searched and worked on this one for over a week. No article I found had answers, only snarky, incomplete, or invalid responses.

Answer 2

The variable you create of object type is set as datatable because the query return a set of rows. Read it from script task as a datatable. And assign the value you want to string variable

Or replace distinct with Top 1 so it return a single row and it can be assigned to string (only one column per variable)