Skip to main content
SQL Server Change Tracking records which rows of a table have been inserted, updated or deleted since a given version number. Kodda’s Incremental Change Tracking load type uses it to read only the rows that changed since the last run — including deletes, which a date- or key-based incremental load cannot detect. This load type is available for SQL Server sources only, and the mapping must have a Business Key.

1. Enable Change Tracking in SQL Server

Change Tracking must be enabled on the database, and then on each table you want to track. Both statements can be run from Browse Data using the source connection. First, the database:
Then each table to be tracked:
Running either statement when tracking is already on returns an error such as Change tracking is already enabled for table ‘Department’ — this is harmless. The table must have a primary key for Change Tracking to be enabled. The login used by the Kodda connection needs VIEW CHANGE TRACKING permission on the table.

2. Set the Load Type

In Edit Mapping, set Load Type to Incremental Change Tracking and save. The mapping below, Std.Redshift.SS_Advw.hr.Department, loads the tracked table into Redshift.
With this load type the Incremental Load Properties section shows only a Next Incremental Value — the Change Tracking version number that the next run will read from, rather than a column value.

3. What Kodda loads

Kodda adds a column named si_change_type_cd to the target table. It holds the last change applied to each row, as reported by SQL Server: I = insert, U = update, D = delete.
On each run Kodda:
  1. Reads the current Change Tracking version from the source (CHANGE_TRACKING_CURRENT_VERSION()).
  2. On the first run, reads the whole table and loads every row with si_change_type_cd = 'I'.
  3. On later runs, queries CHANGETABLE(CHANGES <table>, <last version>) joined to the source table on the Business Key, so only rows changed since the stored version are read.
  4. Merges the changes into the target on the Business Key. Deleted rows stay in the target with si_change_type_cd = 'D'.
  5. Stores the version read in step 1 as the Next Incremental Value.

4. Removing deleted records (optional)

By default a row deleted in the source is kept in the target and flagged D. To delete those rows from the target as well, open Target Properties, add the Load property Hard Delete and turn it on.
With Hard Delete on, Kodda runs DELETE FROM <target> WHERE si_change_type_cd = 'D' at the end of each load.
SQL Server keeps change information only for the database’s retention period (2 days by default). If a mapping has not run for longer than that, its stored version is older than the minimum valid version and the run fails with Incremental value is less than the minimum valid version. Clear the Next Incremental Value to force a full reload, and consider increasing the retention period on the database (ALTER DATABASE … SET CHANGE_TRACKING (CHANGE_RETENTION = …)).
Change Tracking mappings always run with a single extractor; the Number of Extractors setting is ignored.