Handling join of sorted / unsorted result sets

[Catadmin]

All,

Just spotted this on a SQLServerCentral.com enewsletter and thought I'd share.

If you want to join multiple result sets, there are two ways to do it. UNION ALL TRANSFORMATION (which joins unsorted result sets) and MERGE TRANSFORMATION (which joins sorted result sets).

I haven't used either of these myself, but thought I'd pass along the information for those in search of such things. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[MDXer]

I'm currently using the Union All transformation in a number of places. It preforms more like a UNION ALL in T-SQL and less like a join. the MERGE I haven't used.

However since we are talking about joins and the MERGE and MERGE JOIN require a sorted dataflow to use. It is possible to sort your OLE DB source and not have to use the Sort Transformation.

If you aren't aware the way to do it is as follows.

1) In your OLE DB source use SQL command and inclue your "Order By" clause in you Select Statement.
2) Right Click your OLD DB Source object and select Advanced Editor.
3) Select the Input and Output Properties tab.
4) Select OLE DB Source Output and change the IsSorted Property to True
5) Expand the OLE DB Source Output and the Output Columns.
6) Select the column(s) you specified in your SELECT statement and set the SortKeyPosition to the proper order.

NOTE: SSIS is not aware of the actual order by in the SQL Command and purely uses the SortKeyPosition. If these values are in the wrong order it will affect your joins.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck