This sample works only with SQL Server 2008 Enterprise and SQL Server 2008 Developer. This sample will not work with any version of SQL Server earlier than SQL Server 2008, or on any edition other than SQL Server 2008 Enterprise or SQL Server 2008 Developer.

This sample illustrates how to incorporate change data capture technology into an extraction, transformation, and loading (ETL) process that uses Integration Services. This sample is a complete end-to-end sample that includes both workload generation and the
initial load of target tables as part of the sample setup. After the initial load finishes, the master package runs periodically, gathers the changes that have occurred since the last request, and then updates the target replicas to reflect those changes.
The source database is the AdventureWorks2008 database. This sample uses existing tables in the AdventureWorks2008 database to populate comparable destination tables in the CDCSample schema in the AdventureWorksDW2008 database.

Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

You must have installed and have administrative permissions on the AdventureWorks2008 OLTP and AdventureWorksDW2008 databases.

You must install SQL Server Database Engine to install support for the change data capture feature. You must also install Replication when you install SQL Server Database Engine.

If you intend only to run the sample from the command line, you must install Integration Services.

If you want to open and run the sample in SSIS Designer, you must install Business Intelligence Development Studio. For more information about how to install samples, see "Installing Sample Integration Services Packages" in SQL Server Books Online.

Location of the Sample Files

This sample consists of the following files:

The solution file and Readme file.

The project files and packages.

The Transact-SQL script files.

Location of the Solution File and Readme File

The following folder is the default location of the solution file and Readme file for this sample:<drive>:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\Change Data Capture for Specified Interval Package Sample\
The following table provides the complete name and a short description of the files in this folder.

File

Description

Change Data Capture in Integration Services Sample.sln

The solution file for this sample.

Readme_ChangeDataCaptureforSpecifiedIntervalPackageSample.htm

The Readme file for this sample.

Location of the Project Files and Packages

The following folder is the default location of the project files and packages for this sample:<drive>:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\Change Data Capture for Specified Interval Package Sample\Change Data Capture in Integration Services Sample\
The following table provides the complete name and a short description of the files in this folder.

File

Description

Change Data Capture in Integration Services Sample.dtproj

The project file for this sample.

SetupCDCSample.dtsx

The setup package and sample test harness.

MasterCDC.dtsx

The master package that performs an incremental load cycle.

CDCCustomerExtract.dtsx

The child package that gathers the changes to the Customer table for the extraction interval, and then applies those changes to the destination.

CDCCreditCardExtract.dtsx

The child package that gathers the changes to the CreditCard table for the extraction interval, and then applies those changes to the destination.

CDCWorkOrderExtract.dtsx

The child package that gathers the changes to the WorkOrder table for the extraction interval, and then applies those changes to the destination.

Note:

If you did not install this sample to the default location, you will have to modify the package variables,
SQLServerInstallPath and
BasePath , to correspond to the appropriate installation path. The default value of the
SQLServerInstallPath package variable is <drive>:\Program Files\Microsoft SQL Server\. The value of the
BasePath package variable derives from the value of the
SQLServerInstallPath package variable.

Location of the Transact-SQL Script Files

The following folder is the default location of the Transact-SQL script files for this sample:
<drive>:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Package Samples\Change Data Capture for Specified Interval Package Sample\Change Data Capture in Integration Services Sample\Scripts\
The following table provides the complete name and a short description of the files in this folder.

File

Description

CDCSetupTables.sql

The sample setup file.

CDCCustomerInsert.sql

The script that applies Inserts to the CDCSample.Customer table.

CDCCustomerModify.sql

The script that applies Updates to the CDCSample.Customer table.

CDCCreditCardInsert.sql

The script that applies Inserts to the CDCSample.CreditCard table.

CDCCreditCardModify.sql

The script that applies Updates to the CDCSample.CreditCard table.

CDCWorkOrderInsert.sql

The script that applies Inserts to the CDCSample.WorkOrder table.

CDCWOrkOrderModify.sql

The script that applies Updates to the CDCSample.WorkOrder table.

Running the Sample

To run the package, you can use either the dtexec command prompt utility (dtexec.exe) or BI Development Studio.
To run the package by using the dtexec utility:

Open a Command Prompt window.

Use the Change Directory command, cd, to change the directory that contains the dtexec utility.The default location of the dtexec utility is
<drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn
.

For more information about how to use the dtexec utility run a package, see the topic, "dtexec Utility," in SQL Server Books Online.

To run the package in Business Intelligence Development Studio In BI Development Studio, on the
File menu, point to Open, and then click Project/Solution. For Files of type, select
Integration Services Project Files (.dtproj). Then, locate the Change Data Capture for Specified Interval Package Sample folder, and double-click the file,
Change Data Capture in Integration Services Sample.dtproj
. In Solution Explorer, in the SSIS Packages folder, right-click
SetupCDCSample.dtsx , and then click Execute Package.

Sample Packages and Their Components

The sample consists of five packages:
The setup package—SetupCDCSampleThe setup package acts as a test harness for the sample. As a test harness, this package does the following processes: Performs all the required initialization. This includes creating a database snapshot. Generates a data manipulation
language (DML) workload against the source tables while cycling the master package to perform an incremental load of the target tables for a given time period. Monitors the progress of the run, and stops the sample after all the DML workload has been processed
and applied to the destination tables. Validates, at the end of the run, the content of the destination tables. The setup package compares the destination tables with the content of the source tables, and then records the results in the event log. The master
package—MasterCDCThe master package performs an incremental load of the target tables for the interval provided as input. To determine whether to continue with the incremental load, the master package checks the endpoints of the interval: If both endpoints
for the extraction interval are in range, the master package starts the three child packages, which perform the incremental loads for the individual tables. If the high endpoint of the interval is outside the interval, the master package delays processing.
This enables the capture process to catch up. If the low endpoint is outside the interval where change data exists for a source table, the master package logs an error and stops the sample.After all the child packages have finished running, the master package
logs a message to the event log that indicates the sample has finished for the interval. The master package then stops the sample. Three child packages—Extract Customer Data, Extract CreditCard Data and Extract WorkOrder DataThe structure of each of these
child packages is identical: Each child package first creates the query that extracts the changed data. Then, the child package runs a data flow task that performs the incremental load of the changed data. The data flow task uses information in the result
set that the query returns to split the returned rows. The data flow splits the rows into three distinct flows—insert, update, or delete—that correspond to the operation required to apply the row to the destination. The data flow task then runs the tasks that
handle the various flows. The following sections describe the components found in each package.

SetupCDCSample Package Components

The SetupCDCSample package has several annotated functional groups:

Initialize Environment and Enable Change Data Capture

Generate Workload

Perform Initial Load for Target Tables

Prepare for Initial Extraction

Extract and Process Change Data

Validate Incremental Load and Report Run Status These groups help organize the content as described in the following sections.

Note:

To view the annotated functional groups, open the SetupCDCSample package in Business Intelligence Development Studio. To see a larger view of the package, click the four-way arrow in the lower-right corner of the Control Flow tab, and then position the
view window over the package.

Initialize Environment and Enable Change Data Capture

This functional group contains a single Execute SQL task. The purpose of this task is to do the following:

Create the sample source tables from existing AdventureWorks2008 tables, and then enable change data capture for these tables.

Create the target tables in the AdventureWorks2008DW database.

Instantiate the change data capture wrapper functions, and the special functions that run during the initial extraction period. The following table lists the task that this functional group uses.

Generate Workload

This functional group contains six Execute SQL tasks and a Script task:

Each of the three source tables has two Execute SQL tasks: one that generates insert activity against the table and one that generates update activity. Three of the six Execute SQL tasks run after change data capture has been enabled on the source tables.
The remaining three run as each of the first three workload tasks finishes.

When all the Execute SQL tasks have run, the Script task runs. The Script task sets the package variables that indicate that the workload has finished. The following table lists the tasks that this functional group uses.

Name

Element

Purpose

Insert Customer Table

Execute SQL task

Applies inserts to the AdventureWorks2008.CDCSample.Customer table.

Modify Customer Table

Execute SQL task

Applies updates to the AdventureWorks2008.CDCSample.Customer table.

Insert CreditCard Table

Execute SQL task

Applies inserts to the AdventureWorks2008.CDCSample.CreditCard table.

Modify CreditCard Table

Execute SQL task

Applies updates to the AdventureWorks2008.CDCSample.CreditCard table.

Insert WorkOrder Table

Execute SQL task

Applies inserts to the AdventureWorks2008.CDCSample.WorkOrder table.

Modify WorkOrder Table

Execute SQL task

Applies updates to the AdventureWorks2008.CDCSample.WorkOrder table.

Mark Workload Completion

Script task

Sets the following package variables:

The WorkloadCompleted package variable that indicates whether the workload has finished.

The WorkloadEndTime package variable that indicates the time at which the workload finished. This task uses Visual Basic.

Perform Initial Load for Target Tables

This functional group contains one Script task and three Data Flow tasks:

The Script task, Create Database Snapshot, creates a database snapshot of the AdventureWorks2008 database. This snapshot provides the source tables for the initial load of the target tables.

The three data flow tasks then extract data from the snapshot tables and apply this data to the destination tables in the AdventureWorksDW2008 database. The following table summarizes these tasks.

Uses an OLE DB source to extract data from the snapshot table, CDCSample.Customer, and then uses an OLE DB destination to apply the rows to the table, AdventureWorksDW2008.CDCSample.Customer.

Load CreditCard Table From Snapshot

Data Flow task

Uses an OLE DB source to extract data from the snapshot table, CDCSample.CreditCard, and then uses an OLE DB destination to apply the rows to the table, AdventureWorksDW2008.CDCSample.CreditCard.

Load WorkOrder Table From Snapshot

Data Flow task

Uses an OLE DB source to extract data from the snapshot table, CDCSample.WorkOrder, and then uses an OLE DB destination to apply the rows to the table, AdventureWorksDW2008.CDCSample.WorkOrder.

Prepare for Initial Extraction

This functional group contains two Execute SQL tasks and one Script task:

The first Execute SQL task, Verify Capture Process is Started, runs after all the initial loads of the target tables. This task verifies that the change data capture process has auto-started.

The second Execute SQL task, Determine Datetime Base for Initial Extraction Interval, runs if the change data capture process has started. This task determines an initial
datetime value that serves as the base for generating extraction intervals for the incremental loads to follow.

The Script task, Log Capture Not Started Message, runs if the process has not started. This task logs an error in the event log that indicates that SQL Server Agent must be running for the change data capture process to auto-start. This task also stops
the package. The following table summarizes these tasks.

Name

Element

Purpose

Verify Capture Process is Started

Execute SQL task

Examines the table, AdventureWorks2008.cdc.lsntimemapping, to determine whether the capture process has started gathering changes from the transaction log, and then sets the
CaptureStarted package variable accordingly.

Determine Datetime Base for Initial Extraction Interval

Execute SQL task

Extracts the last log sequence number (LSN) that is included in the database snapshot from snapshot metadata, and then sets the
LastLSN package variable to this value. It then delays until the capture process has processed through
LastLSN in the transaction log. The datetime value of the entry in the mapping table with the smallest LSN greater than
LastLSN is then chosen as the datetime base for computing extraction intervals for the run.

Log Capture Not Started Message

Script task

Runs if the CaptureStarted package variable is set to 0, which indicates that the capture process did not auto-start. This task logs an appropriate error message in the event log.

Extract and Process Change Data

This functional group contains a For Loop container that includes two tasks:

A Script task that computes the next extraction interval.

An Execute Package task that runs the package that gathers the change data for the current interval and applies the data to the target tables. The For Loop container maintains the package variable,
IntervalID . The For Loop container initializes the
IntervalID package variable to 0, and then increments this variable by 1 on each iteration of the loop. The loop continues to run until the following conditions are true:

The WorkloadCompleted package variable is set to 1.

The start time for the next iteration exceeds the recorded value for the
WorkloadEndTime package variable. The following table summarizes the components in this functional group.

Name

Element

Purpose

Cycle Master at 10 second intervals

For Loop container

Runs a Script task that computes the new extraction interval, and then runs the package that gathers changes for the interval and applies those changes to the target tables. The container stops processing when all the changes in the generated workload have
been applied to the destination tables.

Set Extract Interval

Script tas k

Sets the package variables that delimit the time interval for the next extraction interval:

The ExtractStartTime variable sets the low endpoint for the interval

The ExtractEndTime variable sets the high endpoint for the interval. To calculate the extraction interval, the task uses the value of the
ExtractEndTime package variable from the previous interval to set the
ExtractStartTime package variable for the current interval. The task then adds 10 seconds to the value of the new
ExtractStartTime package variable, and sets the value of the
ExtractEndTime package variable to this computed value.

Name

Element

Purpose

Run Master to Extract Data

Execute Package task

Runs the MasterCDC package applies the incremental load for a given extraction interval to the destination tables.

Validate Incremental Load and Report Run Status

This functional group contains an Execute SQL task and a Script task:

The Execute SQL task, Check for Mismatch in Replicas, uses SQL CHECKSUM to compare the source tables with the replicas and sets package variables that indicate the run status.

At the end of the run, the Script task, Output Run Completion Status, uses the package variables to report the results of the table comparisons in the event log. The following table summarizes these tasks.

Name

Element

Purpose

Check for Mismatch in Replicas

Execute SQL task

Uses CHECKSUM to compare the replicas to the source tables. Based on this comparison, the task then sets the package variables,
CustomerMismatch , CreditCardMismatch , and
WorkOrderMismatch , accordingly.

Output Run Completion Status

Script task

Outputs a status message to the event log at the end of the run. (This message reports on the parity of the package variables,
CustomerMismatch , CreditCardMismatch , and
WorkOrderMismatch .) Then, sets the run status accordingly.

MasterCDC Package Components

When the SetupCDCSample package runs, five package configurations of type, "Parent package variable", enable the SetupCDCSample package to pass package variables to the MasterCDC package. The following table lists these package variables.

Parent

Child

User::ExtractEndTime

ExtractEndTime

User::ExtractStartTime

ExtractStartTime

User::IntervalID

IntervalID

User::LastLSN

LastLSN

User::BasePath

BasePath

The following table lists the tasks, containers, data sources, data destinations, and transformations that the master package uses.

Name

Element

Purpose

Wait for Capture Process to Process Extraction Interval

For Loop container

Contains the following components:

An Execute SQL task that sets a package variable that indicates the status of the proposed extraction interval relative to the capture process.

A Script task that delays processing if the high endpoint of the interval is outside the interval. This delay enables the capture process to catch up.

A Script task that runs on either of the following conditions:

The low endpoint of the interval predates the interval for which there is valid data.

After repeated delays, the capture process has not caught up. If either of these conditions is true, the task generates an entry in the error log, and causes the package to stop running. If the interval is valid, this container also starts the Execute Packages
tasks that run the child packages that apply the change data to the destination tables.After the child packages complete, this container also runs a Script task that records that the extraction interval has finished in the event log.

Delays processing for 10 seconds when the DataReady package variable is set to 0 and the TimeoutCount is not yet exhausted.(This task runs in the For Loop container, and uses Visual Basic.)

Log Extract Error

Script task

Logs an error to the event log when the DataReady package variable is set to either 1 or 5.(This task runs in the For Loop Container.)

Extract Customer Data

Execute Package task

Runs the CDCCustomerExtract package after the For Loop container finishes, and the
DataReady package variable is set to either 2 or 3.

Extract CreditCard Data

Execute Package task

Runs the CDCCreditCardExtract package after the For Loop container finishes, and the
DataReady package variable is set to either 2 or 3.

Extract WorkOrder Data

Execute Package task

Runs the CDCWorkOrderExtract package after the For Loop container finishes, and the
DataReady package variable is set to 2 or 3.

Log Extraction Complete

Script task

Enters a message in the event log that indicates that the extraction interval has finished.This task runs after all child packages have finished.

CDCCustomerExtract Package Components

When the MasterCDC package runs, four package configurations of type, "Parent package variable", enable the MasterCDC package to pass package variables to the CDCCustomerExtract package. The following table lists these package variables.

Parent

Child

User::ExtractEndTime

EndTime

User::ExtractStartTime

StartTime

User::DataReady

DataReady

User::LastLSN

LastLSN

The following table lists the tasks, containers, data sources and destinations, and transformations that the CDCCustomerExtract package uses.

Name

Element

Purpose

Generate SQL Data Query

Script task

Uses package variables to determine the type of query to run against the OLE DB source. There are two types of queries:

One query runs for the initial extraction interval. This query uses a custom wrapper function that uses an LSN value for the low endpoint and a
datetime value for the high endpoint.

The other query runs for all other extraction intervals. For these intervals, the query uses the standard generated
datetime wrappers. The CDCCustomerExtract package stores this query in the
SQLDataQuery package variable.

Name

Element

Purpose

Process Change Data

Data Flow task

Uses an OLE DB source to query for change data. A data flow component then separates the returned rows into flows—insert, delete, and update—that correspond to the operation required to apply the row to the destination. An OLE DB destination handles the
insert flow. Data Flow components that run SQL commands for each row handle the delete and update flows.

Query for Change Data

OLE DB source

Uses the query that the SQLDataQuery package variable supplies to extract change data. The query uses the connection manager, AdventureWorks2008.

Filter Based on Operation

Conditional Split transformation

Divides the results set that the query returns into three different flows. The transformation uses the value of the
CDC_OPERATION column in each returned row to divide the results set:

If the value is “I”, directs the row to the insert flow.

If the value is “UN”, directs the row to the update flow.

If the value is “D”, directs the row to the delete flow.

Name

Element

Purpose

Customer Inserts

OLE DB destination

Processes the insert flow. Uses the connection manager, AdventureWorksDW2008, to connect to the destination and then inserts the specified rows into the target Customer table.

Customer Deletes

OLE DB Command transformation

Processes the delete flow. Uses the connection manager, AdventureWorksDW2008, to connect to the destination and then deletes the specified rows from the target Customer table.

Customer Updates

OLE DB Command transformation

Processes the update flow. Uses the connection manager, AdventureWorksDW2008, to connect to the destination and then updates the specified rows in the target Customer table.

CDCCreditCardExtract PackageComponents

When the MasterCDC package runs, four package configurations of type, "Parent package variable", enable the MasterCDC package to pass package variables to the CDCCreditCardExtract package. The following table lists these package variables.

Parent

Child

User::ExtractEndTime

EndTime

User::ExtractStartTime

StartTime

User::DataReady

DataReady

User::LastLSN

LastLSN

The following table lists the tasks, containers, data sources, data destinations, and transformations that the CDCCreditCardExtract package uses.

Name

Element

Purpose

Generate SQL Data Query

Script task

Uses package variables to determine the type of query to run against the OLE DB source. There are two types of queries:One query synchronizes the package to the database during the initial extraction interval. This query uses a custom wrapper function that
uses an LSN value for the low endpoint and a datetime value for the high endpoint. The other query runs for all other extraction intervals. For these intervals, the query uses the standard generated
datetime wrappers. The CDCCreditCardExtract package stores this query in the
SQLDataQuery package variable.

Process Change Data

Data Flow task

Uses an OLE DB source to query for change data. A data flow component then separates the returned rows into flows—insert, delete, and update—that correspond to the operation required to apply the row to the destination. An OLE DB destination handles the
insert flow. Data Flow components that run SQL commands for each row handle the delete and update flows.

Query for Change Data

OLE DB source

Uses the query that the SQLDataQuery package variable supplies to extract change data. The query uses the connection manager, AdventureWorks2008.

Filter Based on Operation

Conditional Split transformation

Divides the results set that the query returns into three different flows. This transformation uses the value of the
CDC_OPERATION column in each returned row to divide the results set:

If the value is “I”, directs the row to the insert flow.

If the value is “UN”, directs the row to the update flow.

If the value is “D”, directs the row to the delete flow.

Name

Element

Purpose

CreditCard Inserts

OLE DB destination

Processes the insert flow. Uses the connection manager, AdventureWorksDW2008, to connect to the destination and then inserts the specified rows into the target CreditCard table.

CreditCard Deletes

OLE DB Command transformation

Processes the delete flow. Uses the connection manager, AdventureWorksDW2008, to connect to the destination and then deletes the specified rows from the target CreditCard table.

CreditCard Updates

OLE DB Command transformation

Processes the update flow. Uses the connection manager, AdventureWorksDW2008, to connect to the destination and then updates the specified rows in the target CreditCard table.

CDCWorkOrderExtract Package Components

When the MasterCDC package runs, four package configurations of type, "Parent package variable", enable the MasterCDC package to pass package variables to the CDCWorkOrderExtract Extract package. The following table lists these package variables.

Parent

Child

User::ExtractEndTime

EndTime

User::ExtractStartTime

StartTime

User::DataReady

DataReady

User::LastLSN

LastLSN

The following table lists the tasks, containers, data sources, data destinations, and transformations that the CDCWorkOrderExtract package uses.

Name

Element

Purpose

Generate SQL Data Query

Script task

Uses package variables to determine the type of query to run against the OLE DB source. There are two types of queries:

One synchronizes the package to the database during the initial extraction interval. This query uses a custom wrapper function that uses an LSN value for the low endpoint and a datetime value for the high endpoint.

The other query runs for all other extraction intervals. For these intervals, the query uses the standard generated
datetime wrappers. The CDCCustomerExtract package stores this query in the
SQLDataQuery package variable.

Name

Element

Purpose

Process Change Data

Data Flow task

Uses an OLE DB source to query for change data. A data flow component then separates the returned rows into flows—insert, delete, and update—that correspond to the operation required to apply the row to the destination. An OLE DB destination handles the
insert flow. Data Flow components that run SQL commands for each row handle the delete and update flows.

Query for Change Data

OLE DB source

Uses the query that the SQLDataQuery package variable supplies to extract change data. The query uses the connection manager, AdventureWorks2008.

Filter Based on Operation

Conditional Split transformation

Divides the results set that the query returns into three different flows. The transformation uses the value of the
CDC_OPERATION column in each returned row to divide the results set:

If the value is “I”, directs the row to the insert flow.

If the value is “UN”, directs the row to the update flow.

If the value is “D”, directs the row to the delete flow.

Name

Element

Purpose

WorkOrder Inserts

OLE DB destination

Processes the insert flow. Uses the connection manager, AdventureWorksDW2008, to connect to the destination and then inserts the specified rows into the target WorkOrder table.

WorkOrder Deletes

OLE DB Command transformation

Processes the delete flow. Uses the connection manager, AdventureWorksDW2008, to connect to the destination and then deletes the specified rows from the target WorkOrder table.

WorkOrder Updates

OLE DB Command transformation

Processes the update flow. Uses the connection manager, AdventureWorksDW2008, to connect to the destination and then updates the specified rows in the target WorkOrder table.