You are developing a SQL Server Integration Services (SSIS) package to implement an
incremental data load strategy. The package reads data from a source system that uses the
SQL Server change data capture (CDC) feature.
You have added a CDC Source component to the data flow to read changed data from the
source system.
You need to add a data flow transformation to redirect rows for separate processing of
insert, update, and delete operations.
Which data flow transformation should you use?
A.
Audit
B.
Merge Join
C.
Merge
D.
CDC Splitter
Explanation:
MERGE with Incremental Loads
The change data capture functionality that is new in SQL Server 2008 makes it easier to
perform incremental loads reliably to a data warehouse. As an alternative to using
parameterized OLE DB Command transformations to perform the inserts and the updates,
you can use the MERGE statement to combine both operations.
D
As an alternative to using parameterized OLE DB Command transformations to perform the INSERTS and UPDATES, you won’t need the MERGE statement to combine both operations, because that is why we have a CDC Source Transform.
A CDC Source Transform reads a range of changed data from SQL Server 2012 staging (change) tables and delivers the changes downstream to other SSIS components.
In order to EFFECTIVELY redirect separate rows based on their processing type (insert, update, or delete operations), the data flow transformation solely designed for this purpose is the CDC Splitter.
why C Merge? it is D.
It is D
read the article
https://docs.microsoft.com/en-us/sql/integration-services/data-flow/cdc-splitter