Mark White's BI Blog

Ever wanted to be able to use the OLEDB Destination component in SSIS to load staging tables and  have the underlying data versioned for you? If so, here's a solution which is working very well herein a production environment. Below are some scripts which implement automatic triggers on staging tables so that whenever a newly loaded record collides with the primary key of an existing record, the existing record is quietly moved out into an archive table. In practice, you create two schemas - Staging   - contains all staging tables Archive   - contains all archive (historic) tables When two tables with the same name are detected in these schemas, then triggers are placed onto the table in the Staging schema to handle moving old data into the archive record. The caveat here is that data is only preserved for columns where the column name and type is identical in both the staging and archive table. There are two scripts that make all of this work:

It's nice to be able to predict the name of a primary key constraint based on the name of the table it constrains, but often times during the design of a database, we simply don't have time to worry about such things. Below is a quick T-SQL script which helps to modify all primary key constraints so that they are named according with the table they belong to... e.g.  the table   Dim.Sales will have it's primary key renamed to PK_Dim_Sales To use the script, paste it into management studio, then: press CTRL+T to have the output go to text press F5 to run the script Cut'n'Paste the query results into a new query window press F5 again to run the new query