Checking out new anchor keys in SSIS

Ivo_Clemens

Hi,

In tutorial 14 (INSERTING DATA FOR A DW ENVIRONMENT) a SQL script method is demonstrated which creates the required number of new anchor keys using the kDI_Dish stored procedure. The new keys are returned by the procedure and coupled with the sourcedata.

Is there an elegant way to do this in an SSIS data Flow-task, that is robust against simultaneous updates? (So it will not get confused by other processes also creating new anchors in the same table at the same time)

Roenbaeck

Hi,

I am not an SSIS expert, so there may be more elegant ways of doing this, but if you use the stored procedure to check out a number of keys, those identities will be taken and will not overlap even if several processes call the procedure simultaneously.

The risk with this approach is that you may be left with gaps in your anchor identity number series if your loading should fail after the check-out and you roll back. This is due to the behaviour of identity columns in SQL Server.

Another, but slower, way is to use the insert trigger on the latest view instead. It should be transaction safe.