XML Path SQL在SSIS 2012中不起作用

fThe SQL below has been working for years in a 2008 SSIS package:

f下面的SQL在2008 SSIS包中已经使用了多年：

SELECT 
BofAAcctNumber AS 'td'
,''
,ReferenceNumber AS 'td'
,''
,CheckNumber AS 'td'
,''
,CheckAmount AS 'td'
FROM dbo.bai2_CheckStatementError
    WHERE BenefitID IS NULL
FOR XML PATH('tr')

In SSMS it returns expected results as below

在SSMS中，它返回预期结果，如下所示

<tr><td>999999999</td><td>88888888888</td><td>123456</td><td>999.00</td></tr>

Using an Execute SQL Task (Single Row into a string variable) in 2012 SSIS it kicks a DBNull error: The type of the value (DBNull) being assigned to variable "User::strHTMLTable" differs from the current variable type (String)

在2012 SSIS中使用执行SQL任务（单行转换为字符串变量）它会引发DBNull错误：分配给变量“User :: strHTMLTable”的值（DBNull）的类型与当前变量类型（String）不同

Any criticism or comments welcome, thank you.

欢迎任何批评或评论，谢谢。

1 个解决方案

#1

Specify 'Result' in the 'Result Name' field in the 'Result Set' section and use:

在“结果集”部分的“结果名称”字段中指定“结果”，并使用：

SELECT
    CAST
        (
            (
                SELECT 
                    BofAAcctNumber AS 'td'
                    ,''
                    ,ReferenceNumber AS 'td'
                    ,''
                    ,CheckNumber AS 'td'
                    ,''
                    ,CheckAmount AS 'td'
                FROM dbo.bai2_CheckStatementError
                WHERE BenefitID IS NULL
                FOR XML PATH('tr')
            )
        AS nvarchar) Result

(You could also use varchar if appropriate.)

（如果合适，您也可以使用varchar。）

#1