Hi,

I am looking for some advice on the best way to implement a solution. Some background info might help.

I am using MS SQL 2000.

2 databases are involved in the solution: Human Resource (HR) and Time Tracking (TT)

The TT db is also tracking vacations balance (in hours) but we want to turn off its "Accrual" feature because it does not meet our business requirement.

The HR db already have the information to compute the vacation accrual between two dates for each employee

What I have so far :

An SQL user-defined function that compute the accrual for an employee based on 2 dates; typically the last accrual and today, but having parameters is easier for testing.

An SQL stored procedure in TT that received the employee id and accrual and will first increase the vacation balance and then insert a trace in an audit table.


My intention is to create another stored procedure in HR that will received an employee ID, then figure out the last accrual for this employee, call the function to compute the new accrual and then call the TT store procedure to record the accrual. I then want to use a Data Driven Task in DTS to loop throug a view of all active employee and call this store procedure.


Problem is that I also need to create a log (text file) of all activities, error and actual accruals by employee. I can program the VBScript to write the log file. I can program the store procedure to return as output parameters the result of the computation and the success/Error codes. But I have not figured out how to get that information back in to DTS. I thought of using multi-phases tasks and programing some logic in the Post-row transform event.

Constraints: I am a fervent believer of well structure system where componnets have a specific responsibiliies, much easier to test and reuse. I my view, writing to the log is a responsibility of the "looping" program. But if DTS can't get the information, then I will need to write a master store procedure with a cursor that will loop and write the log. And then simply schedule a job to call this procedure. But then I will be reinventing the wheel has I will need to implement features already in DTS....