SSIS: Merge vs Union All
<p><em>Disclaimer: I'm in the process of learning SSIS - so forgive me if this is an obvious answer, but I couldn't find anything on searching.</em></p>

<p>It seems that the <strong>Merge</strong> transformation is very similar to the <strong>Union All</strong> transformation, with the following differences:</p>

<ol>
<li>Merge can only merge 2 sources of data, whereas Union All supports more (I'm not sure how many - my instructor says 256 but there's no sources to confirm that - and MSDN doesn't mention an upper limit).</li>
<li>Merge requires inputs to be sorted whereas Union All does not.</li>
</ol>

<p>Apart from these differences, there doesn't seem to be much else that is different. They are both partially blocking, and from my limited tests, I couldn't see any noticeable difference in performance either.</p>

<p>Everywhere I've searched, I've found that the recommendation is to use "Merge" whenever possible, and to use "Union All" if there are more than 2 data sources, or if the source data is unsorted.</p>

<p>My question is, why? Why would I not just <strong>use Union All everywhere</strong>? What is the point of Merge?</p>

<p><em>Once again, I'm sorry if this is a very basic/newbie question, but my instructor doesn't know the answer, and searching hasn't been very fruitful either.</em></p>

<p>Thanks!</p>

<p><strong>Note:</strong> <em>It seems (from some other SO answers that people are mixing up "Merge" with "Merge Join". These are two different things.</em></p>