Share via

Process Inserts, Updates, and Deletes

  • Article

In the data flow of an Integration Services package that performs an incremental load of change data, the second task is to separate inserts, updates, and deletes. Then, you can use appropriate commands to apply them to the destination.

Note

The first task in designing the data flow of a package that performs an incremental load of change data is to configure the source component that runs the query that retrieves the change data. For more information about this component, see Retrieve and Understand the Change Data. For a description of the overall process for creating a package that performs an incremental load of change data, see Change Data Capture (SSIS).

Associating Friendly Values to Separate Inserts, Updates, and Deletes

In the example query that retrieves change data, the cdc.fn_cdc_get_net_changes_<capture_instance> function returns only the column of metadata named __$operation. This metadata column contains an ordinal value that indicates which operation caused the change.

Note

For more information about the query that uses calls the cdc.fn_cdc_get_net_changes_<capture_instance> function, see Create the Function to Retrieve the Change Data.

Matching an ordinal value to its corresponding operation is not as easy as using a mnemonic of the operation. For example, 'D' can easily represent a delete operation and 'I' represent an insert operation. The example query that was created in the topic, Creating the Function to Retrieve the Change Data, makes this conversion from an ordinal value to a friendly string value that is returned in a new column. The following segment of code shows this conversion:

    select 
        ...
        case __$operation
            when 1 then 'D'
            when 2 then 'I'
            when 4 then 'U'
            else null
         end as CDC_OPERATION

Configuring a Conditional Split Transformation to Direct Inserts, Updates, and Deletes

To direct rows of change data to one of three outputs, the Conditional Split transformation is ideal. The transformation just checks the value of the CDC_OPERATION column in each row and determines whether that change was an insert, update, or delete.

Note

The CDC_OPERATION column contains a friendly string value derived from the numeric value in the __$operation column.

To split inserts, updates, and deletes for processing by using a Conditional Split transformation

  1. On the Data Flow tab, add a Conditional Split transformation.

  2. Connect the output of the OLE DB source to the Conditional Split transformation.

  3. In the Conditional Split Transformation Editor, in the lower pane of the editor, enter the following three lines to designate the three outputs

    1. Enter a line with the condition CDC_OPERATION == "I" to direct inserted rows to the output for inserts.

    2. Enter a line with the condition CDC_OPERATION == "U" to direct updated rows to the output for updates.

    3. Enter a line with the condition CDC_OPERATION == "D" to direct deleted rows to the output for deletes.

Next Step

After you split the rows for processing, the next step is to apply the changes to the destination.

Next topic: Apply the Changes to the Destination

Integration Services icon (small) 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:


For automatic notification of these updates, subscribe to the RSS feeds available on the page.

See Also

Tasks

Split a Dataset by Using the Conditional Split Transformation

Concepts

Conditional Split Transformation