XML Path SQL not working in SSIS 2012

Question

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

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

<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)

Any criticism or comments welcome, thank you.

Answer 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 -- or nvarchar(x) - as Ann L. points out, nvarchar will truncate the string to 30 characters

(You could also use varchar if appropriate.)