Retrieving and Understanding the Change Data
In the data flow of an Integration Services package that performs an incremental load of change data, the first task is to run the query that retrieves the change data. You execute this query inside a source component in a Data Flow task. You can then use downstream transformations and destinations to apply the change data to your destination.
The creation of a query that contains a table-valued function is the third step in the process of creating a package that performs an incremental load of change data. For more information about this query, see, Creating the Function to Retrieve the Change Data. For a description of the overall process for creating a package that performs an incremental load of change data, see Improving Incremental Loads with Change Data Capture.
To see complete, end-to-end samples that demonstrate the use of change data capture in packages, see the Change Data Capture for Specified Interval Package sample and the Change Data Capture since Last Request Package sample on Codeplex.
In the data flow of the package, you retrieve the change data, separate the rows based on the type of change that occurred, and then apply the changes to the destination.
To add a Data Flow task to the package
In Business Intelligence Development Studio, on the Control Flow tab, add a Data Flow task.
Connect the preceding task that prepared the query string to the Data Flow task.
The source component uses the query string that was prepared and stored in a variable to calls the table-valued function that retrieves the changed data.
For more information about the query string that was prepared and stored in a variable, see Preparing to Query for the Change Data. For more information about the table-valued function that retrieves the change data, see Creating the Function to Retrieve the Change Data.
To configure an OLE DB source to retrieve the change data
In Business Intelligence Development Studio, on the Data Flow tab, add an OLE DB source.
In the OLE DB Source Editor, on the Connection Manager page, select the following options:
Configure a valid connection to the source database.
For Data access mode, select SQL command from variable.
For Variable name, select User::SqlDataQuery.
In the OLE DB Source Editor, on the Columns page, make sure that all the columns that you want are mapped to output columns.