CODEWITHSUNDEEP
ssis
Roger Dodger
Roger Dodger

Reputation: 987

SSIS convert object variable to string in expression builder

I have a query in my ssis package that uses xml path to return a single row of semi colon separated values in a execute sql task. I have a user var called email_list that is then set to the ResultSet as a Single row. But when I run it, I get an error:

The value type (___ComObject) can only be converted to variables of type object

I realize that the variable email_list has to be set to type Object, but if I do that, and then try to use that in the ToLine field in expression builder of email task, the expression doesn't evaluate and throws an error.

I've seen articles on here that give a script task solution but I don't want any scripts - how can this be accomplished in the expression builder itself by converting/casting the object to string?

If it can't be done in the expression builder, what is the workaround?

My query in the execute sql task is:

SELECT STUFF((SELECT ';' + email 
        FROM booklist..books
        FOR XML PATH('')) ,1,1,'') AS email_address

Upvotes: 0

Views: 7646

Answers (2)

Ajeet Verma
Ajeet Verma

Reputation: 1123

If you are taking the input from SQL query then you can change it into String using the cast function ..

From Proc output ::
Select @output 
we can take it this way,
Select cast(@output as varchar)

Upvotes: 0

billinkc
billinkc

Reputation: 61249

The expression language doesn't support object types. In your case, you should be able to cast the resultant into a string data type

SELECT CAST(STUFF((SELECT ';' + email 
        FROM booklist..books
        FOR XML PATH('')) ,1,1,'') AS varchar(8000)) AS email_address

Upvotes: 1

Related Questions