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.

Hadi Hadi · Accepted Answer · 2016-10-27T21:32:59

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)

SSIS variable of object type to string conversion

1 Answers