SSIS执行含FOR JSON子句的SQL时，变量赋值失败问题排查

嘿，这个问题我碰过好多次了，你的错误提示已经把原因说得明明白白：你试图把DBNull类型的值塞给String类型的变量，SSIS对变量类型的要求是严格的，除了Object类型，其他类型不能中途变更，所以直接触发了报错。

那为啥测试查询没问题？因为测试查询肯定能返回一个非空的JSON字符串，而你的实际查询——虽然你觉得应该返回结果，但实际上根本没匹配到任何符合条件的记录，所以json_data字段是NULL，这就触发了类型不兼容的错误。

先搞清楚为啥实际查询没返回数据 #

你可以先把实际查询单独拿到SSMS里跑一遍，看看是不是真的返回NULL。大概率是你的WHERE条件太苛刻，把所有数据都过滤掉了：

• 先检查a.id = '4CBE7065-4893-40F5-AD0B-9746C84A822A'这个ID在application表中真的存在吗？会不会打错了字符？
• 关联条件p.[ID] = [a].[PERSON]有没有问题？比如PERSON字段是不是外键，类型和person表的ID匹配吗？
• 那个NOT IN子查询要小心：如果SELECT [record] FROM [tag] WHERE ([tag] IN ('test'))返回了NULL值，那整个p.[id] NOT IN (...)的条件会直接不成立，导致没有数据返回；或者这个子查询刚好包含了你要找的person的ID，直接把它排除了。

怎么解决这个问题？ #

给你两个靠谱的方案：

1. 强制让查询返回非空的JSON字符串（推荐） #

修改你的SQL语句，用ISNULL或者COALESCE把NULL结果替换成一个合法的空JSON对象（比如'{}'），这样哪怕没匹配到记录，返回的也是String类型的值，就不会触发DBNull的问题了：

SELECT TOP 1 
    ISNULL(
        CAST( 
            ( SELECT [FIRST] AS [FirstName] 
              FROM [person] [p] 
              JOIN [application] [a] ON [p].[ID] = [a].[PERSON] 
              WHERE ( (p.[id] NOT IN (SELECT [record] FROM [tag] WHERE ([tag] IN ('test')))) 
                     AND a.id = '4CBE7065-4893-40F5-AD0B-9746C84A822A' ) 
              ORDER BY p.[last], p.[first] 
              FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER 
            ) AS NVARCHAR(MAX)
        ), '{}'
    ) AS json_data

2. 把变量类型改成Object（不推荐，除非必要） #

如果你不想改SQL，可以把currentAppJSONData变量的类型改成Object，这样它既能接受String也能接受DBNull。但后续用这个变量的时候，你得额外加判断处理NULL的情况，不然下游任务可能会出别的问题，所以还是第一种方案更稳妥。

额外提醒 #

SQL Server的FOR JSON子句有个小坑：如果没有匹配到任何行，它会返回NULL，而不是空数组或者空对象。所以在SSIS里用这类查询时，一定要提前处理NULL的情况，避免踩类型不匹配的坑。

内容的提问来源于stack exchange，提问作者Gerald