“不存在"在 SSIS 数据流中 [英] "NOT EXISTS" in SSIS data flow

问题描述

我有一个数据流，我需要将来自两个不同来源的数据合并到一个目标表中，但也只想插入唯一值而忽略现有值.

I have a data flow where I need to merge the data from two distinct sources into a target table, but also want only to insert unique values and disregard existing values.

行键由两列组成，我将其转换为 SHA1 哈希值以便于合并.

The row key is made up of two columns, which I turned into a SHA1 hash to make it easier to merge.

我执行NOT EXISTS"的方式是通过左外连接"合并连接，我得到正确的连接，然后我使用条件拆分来仅获取空值.

The way I am doing the "NOT EXISTS" is through "left outer join" merge joins, I get the right join and later I use a conditional split to get only the null values.

每个源都按哈希排序.现有数据源多播到两个连接以检查现有值，我只想要新值.

Every source is sorted by the hash. The existing data source is multicast to both joins to check for existing values, I only want new ones.

我遇到的问题是，尽管至少对我来说，一切看起来都很好，但我仍然遇到唯一的密钥违规(source1 和 source2 产生完全不同的密钥，并且数据集中的密钥是唯一的)并且我无法追踪问题.

The problem I'm having is that even though, to me at least, everything looks OK I am still getting unique key violations (both source1 and source2 produce entirely different keys and the keys in the data sets are unique) and I can't trace the issue.

我尝试通过添加更多种类来减慢一切，即使在现有数据"源完成后，它仍然会产生唯一的键违规.

I tried slowing everything down by adding more sorts and even after the "existing data" source finishes it still produces unique key violations.

推荐答案

如果您想执行 NOT Exists 操作，则应该使用配置了重定向不匹配输出"的完整缓存中的 LOOKUP 组件.它的性能相当不错.

If you want to perform NOT Exists operation, LOOKUP component in full cache with "Redirect no match output" configured is the one you should use. Performance are pretty good with it.

这个例子可以帮助:http://www.rad.pasfu.com/index.php?/archives/46-SSIS-Upsert-With-Lookup-Transform.html

希望对您有所帮助

这篇关于“不存在"在 SSIS 数据流中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！