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 seperate 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

  1. Generate Delta – compare MyTable to MyTable_Previous and put the results in MyTable_Delta.
     
  2. 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 menti0oning 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!

About: Carol

I've been doing IT for 30 years, and IdM for 15. I live in Australia and build IdM solutions based on Microsoft Identity Manager. I also play the violin, but that doesn't help much with the IdM solutions.


One thought on “SQL 2005 MA: Export and Delta Import”

  1. can we use the same “delta import” feature/trick to speed up import process from our actual “SOURCE” data sources?

    In the beginning of this blog you mentioned the scenario “After exporting data to a SQL table you must follow with an Import step”, so i got confused whether its only meant for the import process that has to take place after “Exporting” to a data source only, or is it also applicable to data sources only being used to import data in CS.

Comments are closed.