0

I have a contract table with BeginDate fields but no end date on a SQL Server 2000 database (cringe). I'm using an SSIS package designed in the 2008 environment to move data from this table into a staging db for a data warehouse. I need to compute the end date.

The end date is the next contract start date for the customer minus one day.

In anything but SQL Server 2000 this would be easy enough to do with a CTE. I used one strategy that used Row Number to merge to sets, even that won't work here.

My strategy will be to pull the dates through a datasource component. Something like this:

SELECT CustomerId, ContractStartDate FROM Contracts

Then, I'll use a script component and override the ProcessInput method to loop through the result set and add the end date as an output value.

This seems like a pathetic method, but given my constraints, is probably the best method. Can anyone think of any other options?

Stickman
  • 138
  • 7

1 Answers1

1

After some further research I found that it isn't possible to traverse the input buffer at will.

The solution is a bit counter-intuitive to the question. Instead of identifying and modifying the next row, the best you can do is modify the current row based on the previous value. This means that the data will need to be evaluated from the greatest date to the smallest date within the customer id.

For the sake of continuity, I'll keep my example within the context of the original question.

Assume we have are input data for the script component. Add a sort component to order the record set by CustomerID, then by ContractStartDate descending.

Then in the script component, add CustomerID and ContractStartDate as input and add ContractEndDate as an output value.

Override the Input0_ProcessInputRow. The code to do this is generated by default.

Add some properties to keep track of the previous start date and previous customer, and set the current row's end date value based on the previous row.

Your script will look something like this:

public DateTime? PreviousRowStartDate { get; set; }
public string PreviousRowCustomerID { get; set; }
public int KnownContractPeriod { get; set; }
int defaultContractPeriod = 12;

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    if (PreviousRowCustomerID == Row.CustomerID)
    {
        Row.ContractEndDate= PreviousRowStartDate.Value.AddDays(-1);
    }
    else
    {
        Row.ContractEndDate= Row.ContractStartDate.AddMonths(defaultContractPeriod).AddDays(-1);
    }
    PreviousRowCustomerID = Row.CustomerID;
    PreviousRowStartDate = Row.ContractStartDate;
}

This breaks down when dealing with the very first contract (the customers latest). For this reason, I've included a default contract period.

This problem might actually come up to bite me, but it's difficult to say right now as most contracts here are 12 months in length.

Stickman
  • 138
  • 7