The Trouble with SSIS Sorting

re: The Trouble with SSIS Sorting

Andrew Ingram — Wed, 03 Feb 2010 14:15:29 GMT

One point to note on sorting using an order by in the original select is that if you need to sort later in the transform and join the results, the way the source database sorts must be the same as the way SSIS sorts - letters before numbers and vice versa being the prime example.

re: The Trouble with SSIS Sorting

Josh Grant — Thu, 27 May 2010 14:01:02 GMT

Expanding on Andrew's point, I've found that it is UNSAFE to rely entirely upon database sorting, and I am now avoiding one particular data flow component which requires sorted inputs.

Here's an example you can build yourself...

- Locate (or create) a SQL Server database with the SQL_Latin1_General_CP1_CI_AS (case insensitive) collation

- Create a data flow with two OLEDB sources, pointing to these two queries. Note that query #2 is identical to query #1, with one extra row.

-- Query #1

SELECT 'ABb' AS Col1,

1 AS Query1RowNumber

UNION ALL

SELECT 'AbB',

ORDER BY Col1

-- Query #2

SELECT 'ABb' AS Col1,

1 AS Query2RowNumber

UNION ALL

SELECT 'AbB',

UNION ALL

SELECT 'Ab',

ORDER BY Col1

- Using the advanced editor, set the "IsSorted" property on each data source, and set the "SortOrder" to 1 for the Col1 column

- Add a merge join component, join type "full outer join". Make "Col1" the join key, and allow Query1RowNumber and Query2RowNumber to pass through.

- Add a destination of some kind, and put a data viewer on the output of the merge join

The expected result is this, right?

Query1RowNumber Query2RowNumber

--------------- ---------------

1 1

2 2

NULL 3

What you actually get is:

Query1RowNumber Query2RowNumber

--------------- ---------------

NULL 3

NULL 2

1 1

2 NULL

Row number 2 (Col1 = 'AbB') isn't matched in the full outer join, and it is returned as two "unique" rows!

My conclusion from this is that SSIS components which require sorted inputs actually require "SSIS-sorted" inputs, or they can return unexpected results!

re: The Trouble with SSIS Sorting

Mike — Sun, 10 Oct 2010 15:39:13 GMT

Microsoft is promoting the idea that doing everything the memory is great. I does definitely speed up the process plus it help large companies to sell hardware and memory. Sorting algorithms are very well documented and it is difficult to invent something new.

When working with large datasets there are ways of sorting data without buying new hardware

More information

http://www.etl-tools.com/etl-tools/processing-data/sorting.html

re: The Trouble with SSIS Sorting

justins707 — Wed, 27 Oct 2010 23:22:05 GMT

I think the diff is that you are using a UNION ALL. If you change the Source SQL to use UNION (without ALL). Then the results look correct,

null 3

1 1

2 2

Sort Order is null, Ab, ABb, AbB.

re: The Trouble with SSIS Sorting

Chris Nyberg — Thu, 17 Feb 2011 05:49:21 GMT

With Ordinal Technology's Nsort for SSIS sort component, you can quickly sort data sets even if they do not fit in memory. The Nsort component uses a temporary file to avoid the page faulting coma the standard SSIS component can fall into. See http://www.ordinal.com/ssis.html

re: The Trouble with SSIS Sorting

Jemsy Villanera — Thu, 15 Mar 2012 21:11:18 GMT

Good Post, thanks It help me a lot :)

re: The Trouble with SSIS Sorting

Emil Glownia — Mon, 19 Nov 2012 11:14:45 GMT

Interesting post and comments.... I was looking to do some research before I create some more videos for our SSIS Transformations Tutorial and got more ideas than I expected.

I got also interested in why removing ALL would make a difference... and that is only because Case Insensitive collation was used so it is "random" sorting or more precisely UNION and UNION ALL uses different sort for "the same" values.

with Case Insensitive this query:

SELECT 'ABb'AS Col1, 1 AS Query2RowNumber

UNION ALL

SELECT 'AbB',

UNION all

SELECT 'Ab',

ORDER BY Col1

Returns 3,2,1 but with DESC at the end

SELECT 'ABb'AS Col1, 1 AS Query2RowNumber

UNION ALL

SELECT 'AbB',

UNION all

SELECT 'Ab',

ORDER BY Col1 desc

Result is not reverse 1,2,3 but 2, 1, 3... if I take out ALL than result is 1, 2, 3 because 1 and 2 is the same value (with case insensitive collation), obviously Case Sensitive Collation should be used instead but this is a good starting point for me to make some SSIS test ;)

Regards

Emil