Skip to main content

Posts

Showing posts from August, 2011

Record Versions in ETL staging areas

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 tablesArchive   - 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: A stored procedure, responsible for g…

Conforming Primary Key Names

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 textpress F5 to run the scriptCut'n'Paste the query results into a new query windowpress F5 again to run the new query