Implementing a Data Extraction Solution

Use Change Tracking

Change Tracking is another SQL Server technology you can use to record the primary key of records that have been

modified and extract records based on a version number that is incremented each time a rowis inserted, updated, or

deleted. To use Change Tracking, you must log the version that is extracted, and then compare the logged version

number to the current version in order to identify modified records during the next extraction.

Change Tracking is an appropriate technique when:

•

•

The data source is an SQL Server 2008 or later database.

You need to extract the latest version of a row that has been modified since the previous extraction, but you

do not need a full history of all interim versions of the record.

Considerations for Handling Deleted Records

If you need to propagate record deletions in source systems to the data warehouse, you should consider the

following guidelines

•

•

You need to be able to identify which records have been deleted since the previous extraction. One way to

accomplish this is to store the keys of all previously-extracted records in the staging area and compare them

to the values in the source database as part of the extraction process. Alternatively, Change Data Capture

and Change Tracking both provide information about deletions, enabling you to identify deleted records

without maintaining the keys of previously extracted records.

If the source database supports logical deletes by updating a Boolean column, to indicate that the record is

removed, then deletions are conceptually just a special form of update. You can implement custom logic in

the extraction process to treat data updates and logical deletions separately if necessary.

Extracting Rows Based on a Datetime Column

If your data source includes a column to indicate the date and time each record was inserted or modified, you can

use the high water mark technique to extract modified records. The highlevel steps your ETL process must perform to

use the high water mark technique are :

1. Note the current time.

2. Retrieve the date and time of the previous extraction from a log table.

3. Extract records where the modified date column is later than the last extraction time, but before or equal to

the current time you noted in step 1. This disregards any insert or update operations that have occurred

since the start of the extraction process.

In the log, update the last extraction date and time with the time you noted in step 1.

4.

Change Data Capture

The CDC feature in SQL Server Enterprise Edition provides a number of functions and stored procedures that you can

use to identify modified rows. To use CDC, perform the following highlevel steps:

1. Enable CDC in the data source. You must enable CDC for the database, and for each table in the database

where you want to monitor changes. The following Transact-SQL code sample shows how to use the

sp_cdc_enable_db and sp_cdc_enable_table system stored procedures to enable CDC in a database and

monitor data modifications in the dbo.Customers table:

EXEC sys.sp_cdc_enable_db

EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Customers',

@role_name = NULL, @supports_net_changes = 1

In the ETL process used to extract the data, map start and end times (based on the logged date and time of

the previous extraction and the current date and time) to log sequence numbers. The following Transact-

2.

SQL code sample shows how to use the fn_cdc_map_time_to_lsn system function to map Transact-SQL

variables named @StartDate and @EndDate to log sequence numbers:

DECLARE @from_lsn binary(10), @to_lsn binary(10);

SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @StartDate)

SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @EndDate)

Include logic to handle errors if either of the log sequence numbers is null. This can happen if no changes

have occurred in the database during the specified time period. The following Transact-SQL code sample

shows how to check for null log sequence numbers:

IF (@from_lsn IS NULL) OR (@to_lsn IS NULL)

There may have been no transactions in the timeframe

3.

4. Extract records that have been modified between the log sequence numbers. When you enable CDC for a

table, SQL Server generates table-specific system functions that you can use to extract data modifications to

that

the

fn_cdc_get_net_changes_dbo_Customers system function to retrieve rows that have been modified in the

dbo.Customers table:

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Customers(@from_lsn, @to_lsn, 'all')

Transact-SQL

following

sample

shows

table.

code

how

The

use

to

Extracting Data with Change Data Capture

To extract data from a CDC-enabled table in an SSIS-

based ETL solution, you can create a custom control

flow that uses the same principles as the high water

mark

technique described earlier. The general

approach is to establish the range of records to be

extracted based on a minimum and maximum log

sequence number (LSN), extract those records, and log

the endpoint of the extracted range to be used as the

starting point for the next extraction.

You can choose to log the high water mark as an LSN

or a datetime value that can be mapped to an LSN by

using the fn_cdc_map_time_to_lsn system function.

The following procedure describes one way to create

an SSIS control flow for extracting CDC data :

1. Use an Expression task to assign the current time to a datetime variable.

2. Use an SQL Command task to retrieve the logged datetime value that was recorded after the previous

extraction.

time and previously-extracted

3. Use a Data Flow task in which a source employs the fn_cdc_map_time_to_lsn system function to map the

the

current

to

cdc.fn_cdc_get_net_changes_capture_instance function to extract the data that was modified between

those LSNs. You can use the _$operation column in the resulting dataset to split the records into different

data flow paths for inserts, updates, and deletes.

the corresponding LSNs, and

then uses

time

4. Use an SQL Command task to update the logged datetime value to be used as the starting point for the next

extraction.

The CDC Control Task and Data

Flow Components

To make it easier to implement packages that

extract data from CDC-enabled sources, SSIS

includes CDC components that abstract the

underlying CDC

functionality. The CDC

components included in SSIS are :

• CDC Control Task – A control flow

task that you can use to manage

CDC state, providing a straightforward way to track CDC data extraction status.

• CDC Source – A data flow source that uses the CDC state logged by the CDC Control task to extract a range

of modified records from a CDC-enabled data source.

• CDC Splitter – A data flow transformation that splits output rows from a CDC Source into separate data flow

paths for inserts, updates, and deletes.

All the CDC components in SSIS require the use of ADO.NET connection managers to the CDC-enabled data source

and the database where CDC state is to be stored.

Performing an Initial Extraction with the CDC Control Task

When using the CDC Control Task to manage extractions from a CDC-enabled data source, it is recommended

practice to create a package that will be executed once to perform the initial extraction.

This package should contain the following control flow :

1. A CDC Control Task configured to perform the Mark initial load start operation. This writes an encoded

value, including the starting LSN to a package variable, and optionally persists it to a state tracking table in a

database.

2. A data flow that extracts all rows from the source and loads them into a destination—typically a staging

table. This data flow does not require CDC-specific components.

3. A second CDC Control Task configured to perform the Mark initial load end operation. This writes an

encoded value, including the ending LSN to a package variable, and optionally persists it to a state tracking

table in a database.

Performing Incremental Extractions with the CDC Control Task

After the initial extraction has been performed, subsequent extractions should use an SSIS package with the

following control flow :

1. A CDC Control Task configured to perform the Get processing range operation. This establishes the range

of records to be extracted and writes an encoded representation to a package variable, which can also be

persisted to a state tracking table in a database.

2. A data flow that uses a CDC Source, using the encoded value in the CDC state package variable to extract

modified rows from the data source.

3. Optionally, the data flow can include a CDC Splitter task, which uses the _$operation column in the

extracted rowset to redirect inserts, updates, and deletes to separate data flow paths. These can then be

connected to appropriate destinations for staging tables.

4. A second CDC Control Task configured to perform the Mark processed range operation. This writesan

encoded value, including the ending LSN to a package variable, and optionally persists it to a state tracking

table in a database. This value is then used to establish the starting point for the next extraction.

Change Tracking

The Change Tracking feature in SQL Server

provides a number of functions and stored

procedures that you can use to

identify

modified rows. To use Change Tracking,

perform the following high-level steps:

1. Enable Change Tracking in the data

source. You must enable Change

Tracking for the database, and for

each table in the database for which

you want to monitor changes. The

following Transact-SQL code sample

to enable Change

shows how

Tracking in a database named Sales

and monitor data modifications in the

Salespeople table. Note that you can

choose to track which columns were modified, but the change table only contains the primary key of each

modified row—not the modified column values :

ALTER DATABASE Sales

SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON)

ALTER TABLE Salespeople

ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF)

2. For the initial data extraction, record the current version (which by default is 0), and extract all rows in the

source table. Then log the current version as the last extracted version. The following Transact-SQL code

sample shows how to use the Change_Tracking_Current_Version system function to retrieve the current

version, extract the initial data, and assign the current version to a variable so it can be stored as the last

extracted version:

SET @CurrentVersion = CHANGE_TRACKING_CURRENT_VERSION();

SELECT * FROM Salespeople

SET @LastExtractedVersion = @CurrentVersion

3. For subsequent refresh cycles, extract changes that have occurred between the last extracted version and

the current one. The following Transact-SQL code sample shows how to determine the current version, use

the Changetable system function in a query that joins the primary key of records in the change table to

records in the source table, and update the last extracted version:

SET @CurrentVersion = CHANGE_TRACKING_CURRENT_VERSION();

SELECT * FROM CHANGETABLE(CHANGES Salespeople, @LastExtractedVersion) CT

INNER JOIN Salespeople s ON CT.SalespersonID = s.SalespersonID

SET @LastExtractedVersion = @CurrentVersion

4. When using Change Tracking, a best practice is to enable snapshot isolation in the source database and use

it to ensure that any modifications occurring during the extraction do not affect records that were modified

between the version numbers that define the lower and upper bounds of your extraction range.

Extracting Data with Change Tracking

You can create an SSIS package that uses the Change

Tracking feature in SQL Server in a similar way to the

high water mark technique described earlier in this

lesson. The key difference is that, rather than storing

the date and time of the previous extraction, you must

store the Change Tracking version number that was

extracted, and update this with the current version

during each extract operation.

A typical control flow for extracting data from a

Change Tracking-enabled data source includes the

following elements:

1. An SQL Command that retrieves the previously

extracted version from a log table and assigns

it to a variable.

2. A data flow that contains a source to extract records that have been modified since the previously extracted

version and return the current version.

3. An SQL Command that updates the logged version number with the current version.