SSIS合并加入澄清 [英] SSIS Merge Join Clarification

问题描述

SSIS中合并加入背后的逻辑是什么？在SQL Server中，我们不必在加入之前对源进行排序。但为什么在SSIS中有必要？

What is logic behind the merge join in SSIS? In SQL server, we don't have to sort the sources before join. But why it is necessary in SSIS?

推荐答案

从MSDN文档：在Integration Services中，合并和合并连接转换需要为其输入排序数据。必须物理地对输入数据进行排序，并且必须在源或上游转换中的输出和输出列上设置排序选项。如果排序选项指示数据已排序，但数据实际上没有排序，则合并或合并连接操作的结果是不可预测的。

当我读到这样的内容时第一印象是，我认为必须有显着的性能提升或显着简化复杂的逻辑。不确定这是否对您有所帮助，但如果您愿意，我可以获得此处 [ ^ ]。

From MSDN Documentation: In Integration Services, the Merge and Merge Join transformations require sorted data for their inputs. The input data must be sorted physically, and sort options must be set on the outputs and the output columns in the source or in the upstream transformation. If the sort options indicate that the data is sorted, but the data is not actually sorted, the results of the merge or merge join operation are unpredictable.
When I read something like this my first impression is that I believe there must be a significant performance increase or a significant simplifying of complex logic happening under the hood. Not sure if that helps you, but if you want, I got the info here[^].

Sql Server Merge 用于根据匹配或不匹配的条件添加新值或修改现有值。它提高了数据库的性能，因为它只传输了一次数据。





SSIS合并和合并加入：



这里我们需要在数据流任务中应用上述转换之前对数据进行排序。合并以行级方式组合数据，其中合并连接以列级方式组合数据。如果有的话，当我们使用不同的oledb源，excel源和平面文件时，当我们只想将数据组合为单个表（列的内容）时，我们就会进行合并连接转换。当我们想要在行级别中组合数据的时候。



如果说错误，请原谅并纠正我。



谢谢，

Durga

Hi,

Sql Server Merge is used to add new value or modify the existing value basing upon the conditions matches or not matches . It improves the performance of the database as it passes through data only once.


SSIS Merge and Merge Join:

Here we need to sort the data before we apply above transformations in the data flow tasks. Merge combines the data in row wise level where merge join combines the data in column wise level. If at all, when we use different oledb sources, excel sources and flat files and when we just want to combine the data as a single table (interms of columns) we go for merge join transformations. Where as when we want to combine the data in a row level.

Please excuse and rectify me if wrongly said.

Thanks,
Durga

这篇关于SSIS合并加入澄清的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！