10/14/2008
23:13:37
Note: |
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.
Important: |
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. |
Requirements
To
run this sample package, the following requirements must be met:
·
The sample package and
data files that the sample uses must be installed on the local hard disk drive.
·
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 Sample.sln |
The solution file for the Change Data Capture for Specified
Interval Package Sample. |
Readme_ChangeDataCaptureforSpecifiedIntervalPackageSample.htm |
The Readme file for the Change Data Capture for Specified
Interval Package 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 Sample\
The following table provides the complete name and a short
description of the files in this folder.
File |
Description |
||
Change Data Capture Sample.dtproj |
The project file for the Change Data
Capture for Specified Interval Package 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. |
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 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. |
CDCCleanup.sql |
A cleanup script to run to remove all objects created by the
sample. |
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
1.
2.
The default location of the dtexec utility is <drive>:\Program
Files\Microsoft SQL Server\100\DTS\Binn.
3.
dtexec /f "C:\Program Files\Microsoft SQL
Server\100\Samples\Integration Services\Package Samples\Change Data Capture
for Specified Interval Package Sample\Change Data Capture Sample\SetupCDCSample.dtsx" |
4.
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
1.
2.
3.
The
sample consists of five packages:
The setup package—SetupCDCSample
The 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—MasterCDC
The 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
Data
The 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 CDC
wrapper functions, and the special functions that run
during the initial extraction period.
The following table lists the task that this functional group
uses.
Name |
Element |
Purpose |
Create Tables and Enable Change Data Capture |
Execute SQL task |
Creates the sample source tables from existing
AdventureWorks2008 tables: ·
Checks
for any objects that were created in previous runs of the
sample, and deletes these objects, if present. ·
Enables
the AdventureWorks2008 database for change data capture. ·
Creates
the CDCSample schema. ·
Creates
the tables, CDCSample.Customer, CDCSample.CreditCard,
and CDCSample.WorkOrder, in the CDCSample
schema. (These tables mirror the comparable tables in the dbo
schema.) ·
Creates
two helper procedures, generate_wrappers and HexStrToVarbin. Create the target tables in the AdventureWorks2008DW database : ·
Checks
for any objects that were created in previous runs of the
sample, and then deletes these objects, if present. ·
Creates
the CDCSample schema. ·
Creates
the target tables, Customer, CreditCard, and WorkOrder, in the CDCSample
schema. ·
Initializes
the tables, CDCSample.Customer, CDCSample.CreditCard,
and CDCSample.WorkOrder, with a subset of the table
data from their parent AdventureWorks2008 tables. ·
Enables
change data capture on the parent AdventureWorks2008 tables. Instantiates date/time wrapper functions that query for change
data. Defines customer wrapper functions that synchronize the first
extraction interval to the initial load. |
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.
Name |
Element |
Purpose |
Create Database Snapshot |
Script task |
Creates a database snapshot of the AdventureWorks2008 database. This task uses Visual Basic. |
Load Target Customer Table From Snapshot |
Data Flow task |
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 date/time 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.lsn_time_mapping, 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 date/time 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 task |
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. |
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, 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. |
Check for Data |
Execute SQL task |
Sets the DataReady package variable to
one of the following values: ·
0
= Need to wait for capture process ·
1
= Start time predates validity interval ·
2
= Ready for data query ( interval > 1) ·
3
= Ready for first data query ·
5
= Timeout ceiling reached waiting for capture process (This task runs in the For Loop container.) |
Delay |
Script task |
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 date/time
value for the high endpoint. ·
The
other query runs for all other extraction intervals. For these intervals, the
query uses the standard generated date/time wrappers. The CDCCustomerExtract
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. 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. |
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 date/time value for the high
endpoint. The other query runs for all other extraction intervals. For
these intervals, the query uses the standard generated date/time 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. |
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 date/time value for the high endpoint. ·
The
other query runs for all other extraction intervals. For these intervals, the
query uses the standard generated date/time wrappers. The CDCCustomerExtract
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. 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. |
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. |