70-463 · Question #225
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 cap
The correct answer is D. Conditional Split. The CDC Source component in SSIS reads changed rows from SQL Server Change Data Capture tables and tags each row with an operation type indicator in a system column (__$operation): 1=Delete, 2=Insert, 4=Update After, 3=Update Before. To route these rows to separate downstream pro
Question
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?
Options
- ADQS Cleansing
- BMerge Join
- CPivot
- DConditional Split
How the community answered
(24 responses)- A4% (1)
- B13% (3)
- C8% (2)
- D75% (18)
Explanation
The CDC Source component in SSIS reads changed rows from SQL Server Change Data Capture tables and tags each row with an operation type indicator in a system column (__$operation): 1=Delete, 2=Insert, 4=Update After, 3=Update Before. To route these rows to separate downstream processing paths based on their operation type, the Conditional Split transformation is the correct choice - it evaluates user-defined expressions on each row and sends each row to the appropriate output path. For example, one output for inserts, one for updates, and one for deletes. DQS Cleansing is for data quality correction, Merge Join combines two sorted inputs, and Pivot reshapes row data into columns - none of these route rows by CDC operation type.
Topics
Community Discussion
No community discussion yet for this question.