Exam 70-767 topic 1 question 81 discussion

Actual exam question from Microsoft's 70-767

Question #: 81
Topic #: 1

DRAG DROP -
You are developing a Microsoft SQL Server Integration Services (SSIS) package to incrementally load new and changed records from a data source.
The SSIS package must load new records into Table1 and updated records into Table1_Updates. After loading records, the package must call a Transact-SQL statement to process updated rows according to existing business logic.
You need to complete the design of the SSIS package.
Which tasks should you use? To answer, drag the appropriate SSIS objects to the correct targets. Each SSIS object may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:

Show Suggested Answer

Suggested Answer: Explanation
Step 1: CDC Control Task Get Processing Range

Step 2: Mark Processed Range -

Step 3: Data Flow -
The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and lets the user transform, clean, and modify data as it is moved. Addition of a Data Flow task to a package control flow makes it possible for the package to extract, transform, and load data.

Step 4: CDC Source -
The CDC source reads a range of change data from SQL Server 2017 change tables and delivers the changes downstream to other SSIS component.

Step 5: CDC Splitter -
The CDC splitter splits a single flow of change rows from a CDC source data flow into different data flows for Insert, Update and Delete operations.
References:
https://docs.microsoft.com/en-us/sql/integration-services/control-flow/cdc-control-task https://docs.microsoft.com/en-us/sql/integration-services/control-flow/data-flow-task https://docs.microsoft.com/en-us/sql/integration-services/data-flow/cdc-splitter?view=sql-server-2017

by Dieter at May 8, 2020, 6:18 a.m.

Comments

Submit Cancel

Dieter

Highly Voted 5 years, 1 month ago

Although there is no complete question here is my answer: Control Flow: Step 1: CDC Control Task Get Processing Range Step 2: Data Flow Step : CDC Control Task Mark Processed Range Data Flow: Step 1: CDC Source Step 2: CDC Splitter

upvoted 8 times

kma_goal

4 years, 11 months ago

switch step 2 and 3 in Control Flow

upvoted 2 times

pieter94

4 years, 10 months ago

No you should not switch them https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7a436274-1244-4d3f-8233-67216eb91a48/ssis-and-cdc-incorrect-state-at-end-of-8220mark-processed-range8221?forum=sqlintegrationservices

upvoted 2 times

...

Anette

Most Recent 4 years, 9 months ago

CONTROL FLOW Step 1: CDC Control Task Get Processing Range Step 2: Data Flow Step 3: Mark Processed Range DATA FLOW Step 4: CDC Source Step 5: CDC Splitter

upvoted 3 times

...

eceb

4 years, 11 months ago

Step 1: CDC Control Task Get Processing Range Step 2: Mark Processed Range - Step 3: Data Flow - The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and lets the user transform, clean, and modify data as it is moved. Addition of a Data Flow task to a package control flow makes it possible for the package to extract, transform, and load data. Step 4: CDC Source - The CDC source reads a range of change data from SQL Server 2017 change tables and delivers the changes downstream to other SSIS component. Step 5: CDC Splitter - The CDC splitter splits a single flow of change rows from a CDC source data flow into different data flows for Insert, Update and Delete operations.

upvoted 1 times

eceb

4 years, 10 months ago

I correct my previous answer, according to the comments of Dieter and pieter94. control flow 1. get processing range 2. data flow 3. mark processed range data flow 1. cdc source 2. cdc splitter "MarkProcessedRange : This operation is executed after the CDC data flow is completed successfully, to record the last LSN that was fully processed in the CDC run." https://docs.microsoft.com/en-us/sql/integration-services/control-flow/cdc-control-task?view=sql-server-ver15

upvoted 7 times

...