'SQL Server Collation to Match SSIS Ordering

Example values from a SQL Server data set as they appear after an ORDER BY clause:

X0000000-2009
X000000-1-2010
X0000001-2010

If I use ORDER BY Field COLLATE Latin1_General_bin they come out slightly differently as:

X000000-1-2010
X0000000-2009
X0000001-2010

I'm looking to use this data in a Merge Join transformation in SSIS without the need for a Sort transformation, however, a Sort transformation will order them as:

X0000000-2009
X0000001-2010
X000000-1-2010

This is problematic because I need to match the SSIS ordering in my SQL source query in order for the Merge Join to work properly.

Is there a collation I can use in my ORDER BY that is guaranteed to match the SSIS ordering?

sql-serversortingssiscollationsql-server-2019


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions