SSIS LookUp 没有像文档所说的那样处理 NULL

发布于 12-01 17:32 字数 670 浏览 1 评论 0原文

我有一个使用查找的 SSIS 数据流。有时要查找的值(在我的流中,而不是在查找表中)为空。

MSDN 文档说

考虑使用完整缓存,它支持对空值的查找操作。

我正在使用完全缓存(这是默认设置)。

但是当我运行时,我在空行上收到此错误:

行在查找期间没有产生匹配

如果我将结果更改为忽略不匹配,那么它工作正常。但这忽略了所有不匹配的情况。我只想允许空值通过(作为空值)。任何其他不匹配都会导致该组件失败。

我做错了什么?如何将空值写入为空值,但不忽略任何其他错误。

SSISFullCacheScreenshot

SSISErrorScreenshot

(注意:我已经仔细检查了我的查找表。它具有源表中的所有值。它只是没有 NULL 作为值(因为查找值为 null 很奇怪。)

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.

The MSDN Docs say:

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.

SSISFullCacheScreenshot

SSISErrorScreenshot

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

月亮是我掰弯的2024-12-08 17:32:40

我知道这是一个迟到的答案,但对于像我一样搜索此问题的人来说,我发现这是最简单的答案:

在查找连接中,使用 SQL 查询检索数据并添加 UNION SELECT NULL, NULL 到底部。

例如:

SELECT CarId, CarName FROM Cars 
UNION SELECT NULL, NULL

预览将显示在查找中可用的附加行 CarId = NullCarName = 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:

In the lookup connection, use a SQL query to retrieve your data and add UNION SELECT NULL, NULL to the bottom.

For example:

SELECT CarId, CarName FROM Cars 
UNION SELECT NULL, NULL

Preview will show an additional row of CarId = Null and CarName = Null that will be available in the lookpup.

你爱我像她2024-12-08 17:32:40

我从来没有注意到 BOL 中关于完整缓存模式的那一行。您的问题的答案是您已经说过的关于参考数据集中包含 NULL 的内容。一个快速证明是这两个示例数据流。

在此示例中,我生成 4 行数据:1、2、3 和 NULL (Column_isNull = true),并命中包含所有值的内存表,并在数据流中的 Column 和定义在中的 c1 之间执行查找。内存中的表。正如您所描述的那样,它爆炸了

Demo data flow with failed null Lookup

然后我向查找表中添加了一个值 NULL 和瞧,全缓存查找能够将 NULL 与 NULL 匹配。

具有有效 null 查找的演示数据流

要点

要在查找组件中匹配 NULL 输入值,参考数据集必须具有相应的 NULL 值可用,并将缓存模式设置为 FULL。

I had never noticed that line in BOL about the full cache mode. The answer to your problem is what you've already stated about having NULL in the reference set of data. A quick proof would be these two sample data flows.

In this example, I generate 4 rows of data: 1, 2, 3, and NULL (Column_isNull = true) and hit an in memory table that has all the values and perform a lookup between Column in the data flow and c1 defined in the in-memory table. It blows up as you described

Demo data flow with failed null lookup

I then added one more value to the lookup table, NULL and voila, the full-cache lookup is able to match NULL to NULL.

Demo data flow with valid null lookup

Takeaway

To make a NULL input value match in a lookup component, the reference data set must have a corresponding NULL value available as well as have the cache mode set to FULL.

戒ㄋ2024-12-08 17:32:40

要在 SSIS 中解决此问题,可以应用之前的 SO 答案 的替代方法。

在查找转换中,您可以重定向出错的行,然后将它们传递到另一个目标,该目标可以是数据库中相同的预期目标表。

因此,数据库中的目标表仍将接收所有行(下面屏幕截图中的 477 行)。

因此,这种方法避免了将虚拟 NULL 值放入数据库内的查找表中的需要,其权衡是:

  • SSIS 包中的一个额外步骤。
  • 错误行(在本例中为非 NULL 不匹配)将始终加载到目标表中。为了帮助识别这些恶意记录,您可以将目标表导出到 txt 文件中,然后与输入源文件进行比较以查看任何差异。

SSIS 查找中的空值

To work around this issue within SSIS, there is an alternative approach to a previous SO answer which can be applied.

Within the Lookup Transformation, you can redirect rows on error and then pass them to another destination which can simply be the same intended destination table within your database.

Therefore your destination table within the database will still receive all the rows (477 in screen shot below).

This approach therefore avoids the need to put dummy NULL values into your lookup table within your database, with the trade offs being:

  • An extra step within your SSIS package.
  • Error rows (non-NULL non-matches in this case) will always be loaded into the destination table. To help identify these rouge records, you can export the destination table into a txt file and then diff with the input source file to see any differences.

SSIS Null Values in Lookup

野稚2024-12-08 17:32:40

您可以使用条件拆分将数据分成两组,一组给定值为空,其余一组。仅对具有非空值的输出执行查找,然后使用 Union All 将结果与包含空值的数据集组合起来。您仍然可以在查找中捕获不匹配的值,而不必担心必须向查找表中添加空条目

You can use a Conditional Split to break the data into two sets, one where the given value is null and the rest. Only perform the lookup on the output that has non-null values and then combine the results back with the data set that contains the null values using a Union All. You can still trap for unmatched values on the lookup and not worry about having to add a null entry to your lookup table

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文