Change Tracking – incremental data loading into DWH

Posted: October 14, 2012 in MSSQL
Tags:

Task: to create a SSIS package to incremental data loading by means of Change Tracking



The process of creating a SSIS solution consists of the following steps:
1. creating of a SSDT (SQL Server Data Tools) SSIS project
2. enabling of the ‘CHANGE TRACKING’ feature on a database and table level
3. creating of the ‘MySettings’ table and inserting a default value into the ‘LastVersion’ column
4. creating of the ‘Version_Get’ stored procedure
5. getting and setting of the ‘@LastVersion’ and ‘@CurrentVersion’ variables
6. creating of the FAKE DWH ‘BusinessEntity_DWH’ table
7. FAKE inserting into the ‘BusinessEntity’ table
8. incremental data loading into the fake DWH ‘BusinessEntity_DWH’ table (the core of ‘CHANGE TRACKING’)
9. saving of the ‘@CurrentVersion’ variable into the ‘LastVersion’ column
10. disabling of the ‘CHANGE TRACKING’ feature on a table and database level; deleting of created objects

Change Tracking – Control Flow

Figure 1: Change Tracking – Control Flow


Change Tracking – Data Flow

Figure 2: Change Tracking – Data Flow


Source code: SSIS Package (SQL Server 2012 – SSDT)
Note: do not forget to customize the ConnectionString in the Project.params setting

Additional references:
Advertisements
Comments
  1. Shreyans Halani says:

    Hello

    Nice Example for Change tracking,but I face issue for multiple record transaction.By using this example I can Evaluate last record not every records.
    Example.

    if I inserted 3 rows in my employee table
    like
    Insert into employee (id,name,deg) values(1,’TEST1′,SE);
    Insert into employee (id,name,deg) values(2,’TEST2′,SE);
    Insert into employee (id,name,deg) values(3,’TEST3′,SE);

    Then using this example i can get only last record to my staging table.
    Can you please help me on this

    I have searched on net,I got one solution for making temp table and store last version into temp table.But I would like do ETL process without temp table.

    Thanks,
    Shreyans

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s