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.
DQS Cleansing
B.
Merge Join
C.
Pivot
D.
Conditional Split
I think the answer is Conditional Split. Why the answer is PIVOT ?
In previous version there was an option of “CDC Splitter” which is most suiteable, otherwise I agree it could be implemented using a Conditional Split.
Pivot is never used for row-redirection. It performs the same function as in a Pivot-Table in Excel, or the Pivot T-SQL command.
Since CDC Splitter isn’t in the option, the next best option is Conditional Split (would just take some tweaking).
Pivot transformation has one input, one regular output, and one error output. so it will not help in this, only conditional split can provide multiple outputs among provided option