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
Should be CDC splitter.
Merge transformation will bring all information together.
CDC splitter will seperate data into inserts, deletes, updates
D
Yes, D
The question is unclear: the redirecting is done by the splitter but the processing needs to be done with a follow-up component.
There is no component that redirects AND processes data.
The splitter redirects, the MERGE component processes.
“You can use the splitter to connect to pre-defined INSERT, DELETE, and UPDATE outputs for further processing.”
https://msdn.microsoft.com/en-us/library/hh758656(v=sql.120).aspx