Citation:
Loading a Data Mart Table from a
Change Data Capture Change Table
In order to utilize the data in the change table, we need to create an Integration Services
package. This package will use our wrapper function to query data from the change table
and then apply those changes to a target table in the data mart. In this section, we will
explore the control flow and the data flow of a package that does just that.
Control Flow for Loading Data from Change Data Capture
The control flow for an Integration Services package that loads data from change data
capture is shown in Figure 8-12. This control flow has three steps:
1. Finding the start and end dates to use for this data load and creating a query that
uses those start and end dates.
2. Load the data from the change data table into the data mart.
3. Save the end date so the package knows where to start from the next time it is run.
“Get Data Loaded Thru Date” is an Execute SQL task. It runs a query against a table
called DataLoadedThru to get the start and end dates for this data load. The query is:
SELECT CONVERT(nvarchar(50), OrderDataLoadedThru, 121) AS DataLoadStart,
CONVERT(nvarchar(50), DATEADD(d, 1, OrderDataLoadedThru), 121)
AS DataLoadEnd
FROM dbo.DataLoadedThru
This query gets the previous end date to use as the new start date. It assumes the
Integration Services package is run every 24 hours, so it uses the start date plus one
day as the new end date. Care must be taken with this setup to ensure the change data
capture has been processed past the calculated end date by the time this package is run.
This Integration Services package uses two variables to hold the dates returned by
this query. We tell the Execute SQL task that our query will only return one record in
the result set. Then, we set the variable mappings, as shown in Figure 8-13.
A third Integration Services variable uses the DataLoadStart and DataLoadEnd
variables to create a query. This variable is called DataLoadQuery. We set the
EvaluateAsExpression property of this variable to true and set the Expression property to:
"SELECT OrderDate, CustomerAccount, Product, Store, Promotion,
SalesPerson, StoreSales, UnitSales, __CDC_OPERATION AS Operation
FROM dbo.fn_all_changes_dbo_Orders('" + @[User::DataLoadStart]
+ "', '" + @[User::DataLoadEnd] + "', 'all')
ORDER BY __CDC_STARTLSN, __CDC_SEQVAL"
sauf que j'utilise une source de donnée ADO NET non pas OLE DB