why we need to use sort transformation before merg

Question

Can any one please tell me why we are sorting all the records before using merge component or merge join component? What is the Exact reason behind it?

Answer 1

A merge requires sorted inputs because of how it works internally - a single pass through both inputs at the same time is sufficient to produce a merged sorted output. See the MSDN documentation for the SSIS Merge Transformation and Merge Join Transformation for a little more info.

If you don't want or need sorted output you could look at the Union All Transformation that just combines it's inputs into one output without any sorting.

Regards,

Rhys