I am tracking insert changes only on 4 tables. I am using an SSIS package and stored procedures to return incremental changes to all tables to populate a data warehouse.

When the initial data is loaded into the database, the CHANGE_TRACKING_CURRENT_VERSION changes to 1. 

If I then run the SSIS package for loading the DW, it will not return any records as we are using the CHANGE_TRACKING_CURRENT_VERSION as the last sync version. 

Must we do an initial load before we enable change tracking and use CHANGE_TRACKING_CURRENT_VERSION?

Do we need to track changes to each table separately?

Do we need to use our own custom change tracking id?

• Edited by DarrenOD Friday, February 13, 2015 1:52 PM

Yes, you need to initiate the tracking by an initial load, and you do not need the stored proc, use the SSIS CDC feature http://www.mattmasson.com/2011/12/cdc-in-ssis-for-sql-server-2012-2/

Thanks. I am using Change Tracking not Change Data Capture.

I will create a separate package for an initial DW load using default 0. A load will be performed nightly thereafter.

However, I do not understand how we can use the the CHANGE_TRACKING_CURRENT_VERSION as this value will increment each time any table change is made so what we really need to know is the last synced version for each individual table. I am assuming that this is the latest SYS_CHANGE_VERSION.

Is my understanding correct?

I think my only option is to disable and enable Change Tracking after each load which will reset the CHANGETABLE.

I can then continue to use 0 as my change version in my SSIS package and return ALL changes in ALL tables at each run.

• Marked as answer by Katherine XiongMicrosoft contingent staff, Moderator 4 hours 3 minutes ago
• Unmarked as answer by DarrenOD 1 hour 33 minutes ago

I think my only option is to disable and enable Change Tracking after each load which will reset the CHANGETABLE.

I can then continue to use 0 as my change version in my SSIS package and return ALL changes in ALL tables at each run.

• Marked as answer by Katherine XiongMicrosoft contingent staff, Moderator Monday, February 16, 2015 7:23 AM
• Unmarked as answer by DarrenOD Monday, February 16, 2015 9:53 AM

Hi DarrenOD,

It seems that the issue had been solved and thanks for your sharing. It will be very beneficial for other community members who have similar questions.

Id like to mark this issue as "Answered". Please also feel free to unmark the issue, with any new findings or concerns you may have.

Regards,
Katherine Xiong

On reflection, I do not believe this option is suitable. If changes occur during the DW load and the Change Tracking is disabled at this exact time, I will ignore these changes. As we are planning a load each night, this could cause a data consistency problem.

Is there a way to track the last synced version of each individual table? We could then store this value in our own Change Tracking table, use during our load and update the last synced version each time.

Can I use MAX(SYS_CHANGE_VERSION) of each table to give me the previous synced version after each load?

• Edited by DarrenOD 1 hour 4 minutes ago

On reflection, I do not believe this option is suitable. If changes occur during the DW load and the Change Tracking is disabled at this exact time, I will ignore these changes. As we are planning a load each night, this could cause a data consistency problem.

Is there a way to track the last synced version of each individual table? We could then store this value in our own Change Tracking table, use during our load and update the last synced version each time.

Can I use MAX(SYS_CHANGE_VERSION) of each table to give me the previous synced version after each load?

• Edited by DarrenOD Monday, February 16, 2015 10:22 AM

If you load each night then the changes captured by then will be reflected in this run. The new ones until next.

There is nothing to check for as far as I as I can see.

I have been able to successfully track the Previous Sync Version of each table by using the SYS_CHANGE_VERSION of each table. Here is what I did:

1. Create a ChangeTracking table and a Previous Sync Version column for each table. I have 4 tables:

2. Execute SSIS package to perform an initial load of DW. On complete:

Set Previous Sync Version of each table using SELECT MAX(SYS_CHANGE_VERSION) FROM CHANGETABLE(CHANGES dbo.EACH TABLE, 0) AS CT

3. Execute SSIS package to perform incremental load which reads Previous Sync Version of each table from ChangeTracking table and uses a separate stored procedure for each table to return all records after that version. On complete:

Set Previous Sync Version of each table using SELECT MAX(SYS_CHANGE_VERSION) FROM CHANGETABLE(CHANGES dbo.EACH TABLE, 0) AS CT

The MAX(SYS_CHANGE_VERSION) gives us the last sycnced version of each table table.

Arthur, are you saying that the method of disabling and enabling Change Tracking after each load (reset the CHANGETABLE) is better??

• Edited by DarrenOD 1 hour 33 minutes ago