Record Value Frequency

First I’d like to quote my recent tweet on this issue

Have always considered SSIS to be extremely flexible but lately found wrong. Nevertheless, yet to find any ETL too that can beat it

I think I may have been wrong to blame this on SSIS but rather on limitations of TSQL itself. My situation is a need to have frequency of a record value indicated in a column as indices as below

Value
SEQNR
1
1
2
1
2
2
4
1
4
2
4
3

I tried to use Rank() with partition as shown in snippet below but got a different result. Also ‘Bing(ed) this but came up void of a similar issue.

select value, Rank() over (partition by Value order by Value)

This is like to use and inject into SSIS package. Doing this with combination of SSIS task is not quite straightforward.

The only way I could see out of this is to write a CLR function to achieve this which I am feeling so lazy to do but still has to be done.