What is the best way to create change history on a table? RRS feed

  • Question

  • AS-IS

    We have a table A which gets updated daily by an external party background program.

    We have no authority to view or change this external party background program, the following listed what the program does: 

    • 1. Delete any data more than 365 days in table A.
    • 2. Insert new records into this table A.
    • 3. Update new value on some of the columns in table A.


    • We want to keep all the data for 7 years instead of just 1 year.
    • We want to have "version history" for each record updates.


    • I am planning to create a new destination table & to use SQL Server 2008 R2 (SSIS) to achieve this, is this the best way?
    • Which transformation in SSIS should I be using? Or should I be able to achieve just by using T-SQL in SSIS?




    Monday, March 20, 2017 12:13 PM


All replies