Process Inserts, Updates, and Deletes
Applies to:
SQL Server
SSIS Integration Runtime in Azure Data Factory
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
On the Data Flow tab, add a Conditional Split transformation.
Connect the output of the OLE DB source to the Conditional Split transformation.
In the Conditional Split Transformation Editor, in the lower pane of the editor, enter the following three lines to designate the three outputs
Enter a line with the condition
CDC_OPERATION == "I"
to direct inserted rows to the output for inserts.Enter a line with the condition
CDC_OPERATION == "U"
to direct updated rows to the output for updates.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
See Also
Conditional Split Transformation
Split a Dataset by Using the Conditional Split Transformation
Tilbakemeldinger
https://aka.ms/ContentUserFeedback.
Kommer snart: Gjennom 2024 faser vi ut GitHub Issues som tilbakemeldingsmekanisme for innhold, og erstatter det med et nytt system for tilbakemeldinger. Hvis du vil ha mer informasjon, kan du se:Send inn og vis tilbakemelding for