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.
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:
- Generate_Delta – compares the current and previous versions of the table, and uses the differences to generate the Delta table.
- 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.
This package contains two Execute SQL tasks – follow the links to see the SQL queries
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!