SSIS convert object variable to string in expression builder

Question

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

Is there a reason you're storing XML in an variable of Object type instead of String — billinkc
– billinkc, Commented Aug 10, 2016 at 15:37
@billinkc - I used stuff() so I could get a single row of semi colon separated values like so: [email protected];[email protected];[email protected] — Roger Dodger
– Roger Dodger, Commented Aug 10, 2016 at 16:11
Can't you just wrap the stuff call with 'cast(stuff... As varchar(8000)) as ToList' — billinkc
– billinkc, Commented Aug 10, 2016 at 16:21

billinkc · Accepted Answer · 2016-08-10 17:18:53Z

1

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

edited Aug 10, 2016 at 17:18

answered Aug 10, 2016 at 16:24

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

RalfFriedl · Accepted Answer · 2019-09-01 08:12:49Z

0

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)

edited Sep 1, 2019 at 8:12

RalfFriedl

1,1303 gold badges11 silver badges14 bronze badges

answered Sep 1, 2019 at 3:46

Ajeet Verma

1,1231 gold badge9 silver badges27 bronze badges

Collectives™ on Stack Overflow

SSIS convert object variable to string in expression builder

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related