How to rank an union of two result sets?

I am new to SQL Server Integration Services. I need to help here.

If there is only one result set, I could get rank info by putting DENSE_RANK expression inside of my select. My case is that I have two data source. After union of them, I need rank the result set and save result set plus rank info into the destination. I tried to use Derived Column component by using expression DENSE_RANK() over ( order by columnName ). But it does not like it.

Does anyone know how to achieve this?

Thanks!

Dense_rank is not a SSIS function. If you want to use the DB engine, you could write the 2 sets of data to a staging table and then use an additional dataflow with a query that does the rank over the staging table.

Another option would be to look at Jamie's rank transformation:

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Rank-Transform.aspx

|||

Thanks for the info. Do you know where to get the Conchango Runk transform conponent?

|||

Jun Fan wrote:

Thanks for the info. Do you know where to get the Conchango Runk transform conponent?

At the bottom of that post, there is an "Attachment" link. Click on that to get it.