How to generate sequence number for members of each group

Question

I have a table that is similar to this:

CREATE TABLE dbo.ReportGroups (
    ReportName     VARCHAR(50)    NOT NULL,
    GroupName      VARCHAR(50)    NOT NULL,
    SequenceNumber SMALLINT       NOT NULL,
    CONSTRAINT PK_ReportGroups PRIMARY KEY CLUSTERED (ReportName, GroupName)
)

I'm importing into this table from a flat file that just contains the report names and group names, so I need to generate the SequenceNumber. For each report the sequence numbers should restart at 1.

I've seen how to do this with a script task for normal sequence numbers, but restarting for each new report name doesn't seem to be out there. My guess is that I'll need to use the Sort transform and then add code to check for changes in the report name(?) Has anyone done something like this? Any sample code to share?

Answer 1

As I suspected, this was possible by adding a Sort transformation and then adjusting the Script transform. I added the Sort on ReportName->GroupName then added the following code to a Script transform:

public class ScriptMain : UserComponent
{
    short seqNum;
    string reportName;

    public override void PreExecute()
    {
        base.PreExecute();

        seqNum = 0;
        reportName = "";
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        if (Row.MapName != reportName)
        {
            reportName = Row.MapName;
            seqNum = 1;
        }
        else
            seqNum++;

        Row.SequenceNumber = seqNum;
    }
}

I've stripped out the help and comments to make the code a bit more condensed and readable on here.