DSXchange

Hi,
I have a scenario to generate surrogate key in an audit table which has the below columns.

Audit Table
-----------------
Surrogate_key
job_name
job_status,
start time
end time

At the beginning of each sequence I have to insert a record in the audit table with values in columns surrogate_key, job_name, start_time.
At the end of the sequence I have to update the status and end_time columns of the same record.

Now my question is, at the beginning of the job I will insert a record in audit table with some surrogate key number(Eg: 123), while updating the table at the end of load I have to use same surrogate key. How to achive this.
Is there any option to generate surrogate key in sequence level. Please suggest.

Many thanks in advance

You could use a routine activity to invoke one of the SDK key management routines. The routine activity will return the next surrogate key value, which will be available in the return value activity variable for the activity in any downstream activity.