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.

Note

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.

Adding the Data Flow Task

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

1. In Business Intelligence Development Studio, on the Control Flow tab, add a Data Flow task.

2. Connect the preceding task that prepared the query string to the Data Flow task.

Configuring the Source Component to Query for Changes

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.

Note

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

1. In Business Intelligence Development Studio, on the Data Flow tab, add an OLE DB source.

2. In the OLE DB Source Editor, on the Connection Manager page, select the following options:

   1. Configure a valid connection to the source database.

   2. For Data access mode, select SQL command from variable.

   3. For Variable name, select User::SqlDataQuery.

3. In the OLE DB Source Editor, on the Columns page, make sure that all the columns that you want are mapped to output columns.

Next Step

After you have configured an OLE DB source to retrieve the change data, the next step is to start designing the data flow in the package.

Next topic:Processing Inserts, Updates, and Deletes

Stay Up to Date with Integration Services For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet: Visit the Integration Services page on MSDN Visit the Integration Services page on TechNet For automatic notification of these updates, subscribe to the RSS feeds available on the page.