Using a Sort Transform in SSIS

SQL Server MVP Brian Knight demonstrates you how to use a sort transform to remove duplicate data and apply ordering to your data. The sort transform is an interesting alternative to TSQL for this task - after all, you may be working with data that isn't native to SQL Server.

Owner of Pragmatic Works :: Brian Knight, SQL Server MVP, MCSE, MCDBA, is the co-founder of Jumpstart TV and SQLServerCentral.com. Brian has written and co-written 9 technical books. Brian has spoken at conferences like PASS, TechEd and Connections. Brian develops solutions with Pragmatic Works to migrate DTS packages to SSIS.

Howard Wild on 12/1/2008

Am I right in the belief that you have to use the Sort function when feeding into a Merge task? if so you could point out that you cant use sort in the SQL task.

Michael Gorman on 12/5/2008

I wish I had this in more context. I'm not a SQL Server user and had no clue that a sort transform even existed. It's not part of the formal SQL lanugage, so where does it come from? How is it persistent?

Matthew on 12/5/2008

Great as usual, a lot of information in a little time. I knew that you basically shouldn't use a sort transform unless you have a very good reason, and I'm glad you touched on that at the very end.

Venkata Taduri on 2/10/2009

Pretty neat. Thank you.

Holck on 3/10/2009

I'm not sure I understand why "asynchronous" sorting is less desirable... you didn't really cover that. Certainly, the performance of the sort transform is extremely poor compared to sorting at the sql level. Because of the poor performance, I've also had to rework my flows quite a bit. Are there situations where you had to use a sort? Only place I can think of is you need it sorted one way for an earlier transform and then differently for a later one or maybe because it's not a database source. Typically, I've only attempted a sort when I need to use a merge join, and I found that sorting on the sql side and then setting the IsSorted and SortKey values within the Advanced Transform editor that this is easier.

Andy Warren on 3/11/2009

Holck, not all data originally resides in SQL, so one good case for using SORT is when you're working with data from other/multiple sources

Anthony Zackin on 3/14/2009

Does the author not know the difference between synchronous and asynchronous?

Dilli on 4/22/2009

Simply best tutorial

surya kiran on 5/21/2009

Good Video, Thank You

Travis on 6/18/2009

terrible speaker...

Suchu on 9/6/2009

thank you

Kevin Lewis on 12/1/2009

Like you say, if you can order it first then that's best. Useful in flat file feeds or xml...

Chris Manuel on 2/23/2010

excellent!

Anil Inampudi on 5/26/2010

Good one

Torbjörn Håkansson on 5/29/2010

Short and sweet, just what it promised and no extra crap, thanks!

anilbabu on 8/3/2010

good