SQL 2005 MA: Export and Delta Import

I use a lot of SQL MAs. While ILM/MIIS is great at syncing data between directories, it really isn’t that great at calculating and comparing. As much as possible I like to do the data manipulation in SQL, and then keep ILM to what it’s best at – joins and syncs.

After exporting data to a SQL table you must follow with an Import step, allowing ILM to confirm that the data was correctly exported.  For speed and efficiency you will want that confirming Import to be a Delta Import. This post shows you how to do that with SQL 2005.

Approach

You can only enable delta imports from a SQL MA if you have a Delta table. The simplest thing is to compare the table with an earlier version and create a Delta table from the differences.

The trick is then to generate the Delta table in between running the export and delta import steps.

Create Previous and Delta Tables

Let’s start with a table called MyTable.

You need to create an exact copy of this table to put the previous data set into – lets’ call it MyTable_Previous.

And finally you will need a delta table- MyTable_Delta. This has all the same columns as MyTable, plus an extra column called ChangeType, which will contain the strings ‘Add’, ‘Delete’ or ‘Modify’.

SQL newbie tip: If you Edit the table in SQL Server Management Studio it will open a query window with a CREATE TABLE query, which you can then modify to create the extra tables. For the Delta table, as well as changing the table name, add this column definition:

[changeType] [nvarchar](20) NULL

Create SSIS Packages

Next you need to create two SSIS packages:

1. Generate_Delta – compares the current and previous versions of the table, and uses the differences to generate the Delta table.
2. Clear_Delta – I like to separate out the step to clear all lines from the Delta table so I can make sure the import was successful before I run it.

Generate_Delta

This package contains two Execute SQL tasks – follow the links to see the SQL queries

Generate Delta – compare MyTable to MyTable_Previous and put the results in MyTable_Delta. Archive Table – copy MyTable to MyTable_Previous, for the next time.

Clear_Delta

A very simple package – just the one Execute SQL task with a single query line:

truncate table MyTable_Delta

Configure Your MA

Once you have the Delta table you can configure your MA for delta imports. I will just refer you to this post for more info on that.

Configure Run Profiles

Fairly obvious so just mentioning for completeness – you’ll need to create one or more run profiles with a “Delta Import” step.

Running the SSIS packages with the ILM jobs

I now use a vbscript to put all the steps together. Follow the links to see the subroutines.

  If ExportsPending("My_SQL2005_MA") Then
    RunProfile "My_SQL2005_MA", "Export"
    RunSSIS "Generate_Delta"
    If RunProfile("My_SQL2005_MA", "Delta Import and Delta Sync") = 0 Then
      RunSSIS "Clear_Delta"
    End If
  End If

And that’s it!