SSIS LookUp 没有像文档所说的那样处理 NULL [英] SSIS LookUp is not dealing with NULLs like the docs say it should
问题描述
我有一个使用查找的 SSIS 数据流.有时要查找的值(在我的流中，而不是在查找表中)为空.
I have an SSIS data flow that uses a lookup. Sometimes the value to be looked up (in my stream, not in the lookup table) is null.
考虑使用完全缓存，它支持对空值的查找操作.
consider using full caching, which supports lookup operations on null values.
我正在使用完全缓存(这是默认设置).
I am using Full Caching (that is the default).
但是当我运行时，我的空行出现此错误:
But when I run I get this error on my null rows:
在查找过程中没有匹配行
Row yielded no match during lookup
如果我将结果更改为忽略不匹配，则它可以正常工作.但这会忽略所有不匹配项.我只想允许空值通过(作为空值).任何其他不匹配都应该使组件失败.
If I change the result to ignore no-matches then it works fine. But that ignores all no-matches. I just want to allow nulls through (as null). Any other no-match should fail the component.
我做错了什么?如何让空值写为空值，但不忽略任何其他错误.
What am I doing wrong? How can I get nulls to write as nulls, but not ignore any other errors.
(注意:我已经仔细检查了我的查找表.它包含我的源表中的所有值.它只是没有 NULL 作为值(因为查找 null 值很奇怪.)
(NOTE: I have double checked my look up table. It has ALL the values that are in my source table. It just does not have NULL as a value (because it is weird to have a look up value for null.)
推荐答案
我知道这是一个迟到的答案，但对于任何像我一样搜索此问题的人，我发现这是最简单的答案:
I know this is a late answer, but for anyone searching on this like I was, I found this to be the simplest answer:
在查找连接中，使用 SQL 查询检索数据并将 UNION SELECT NULL, NULL 添加到底部.
In the lookup connection, use a SQL query to retrieve your data and add UNION SELECT NULL, NULL to the bottom.
例如:
SELECT CarId, CarName FROM Cars
UNION SELECT NULL, NULL
预览将显示额外的一行 CarId = Null 和 CarName = Null，它们将在查找中可用.
Preview will show an additional row of CarId = Null and CarName = Null that will be available in the lookpup.
这篇关于SSIS LookUp 没有像文档所说的那样处理 NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
