SSIS在表达式生成器中将对象变量转换为字符串 [英] SSIS convert object variable to string in expression builder

问题描述

我的 ssis 包中有一个查询，它使用 xml 路径在执行 sql 任务中返回单行分号分隔值.我有一个名为 email_list 的用户变量，然后将其设置为 ResultSet 作为单行.但是当我运行它时，我收到一个错误:

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:

值类型(___ComObject)只能转换为object类型的变量

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

我意识到必须将变量 email_list 设置为 Object 类型，但是如果我这样做，然后尝试在电子邮件任务的表达式构建器的 ToLine 字段中使用它，则表达式不会计算并引发错误.

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?

我在execute sql任务中的查询是:

My query in the execute sql task is:

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

推荐答案

表达式语言不支持对象类型.在您的情况下，您应该能够将结果转换为字符串数据类型

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

这篇关于SSIS在表达式生成器中将对象变量转换为字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！