Retrieve and Understand the Change Data
Applies To: SQL Server 2016
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.
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.
In SQL Server Data Tools (SSDT), 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.
In SQL Server Data Tools (SSDT), 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.
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: Process Inserts, Updates, and Deletes