Record Value Frequency In SSIS

I blogged last week about a problem on Record Value Frequency in SSIS. I suggested in the blog post on how to go about taking care of this.

“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.”

This I finally had time to do and pasted below is the simple CLR User Defined Functions (UDF)

static readonly Dictionary<SqlString, SqlInt32> myDic = new Dictionary<SqlString, SqlInt32>();

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString SvSequence(SqlString colValue)

    {

        SqlInt32 nextValue = 1;

        if (myDic.ContainsKey(colValue))

        {

            myDic[colValue] += 1;

            nextValue = myDic[colValue];

        }

        else

            myDic.Add(colValue, 1);

        // Put your code here

        return nextValue.ToSqlString();

    }

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString RestartSequence()

    {

        myDic.Clear();

        return new SqlString();

    }

Notice the readonly used in the static declaration for the dictionary. This is enforced by the compiler for static class member as a requirement. I also had to create a reset function which IMHO is not that cool but I need a way to reset this as the value kept increasing each time the UDF is called.

dbo.RestartSequence ;

select AccountId, dbo.SvSequence(AccountId) from dump