Online Access Free 070-767 Exam Questions

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.

Correct 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

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it As a result these questions will not appear in the review screen.
You configure a new matching policy in Master Data Services (MDS) as shown in the following exhibit.

You review the Matching Results of the policy and find that the number of new values matches the new values.
You verify that the data contains multiple records that have similar address values, and you expect some of the records to match. You need to increase the likelihood that the records will match when they have similar address values.
Solution: You decrease the relative weights for Address Line 1 of the matching policy.
Does this meet the goal?

• A.No
• B.Yes

You manage Master Data Services (MDS).
You need to create a new entity with the following requirements:
* Maximize the performance of the MDS system.
* Ensure that the Entity change logs are stored.
You need to configure the transaction Log Type settings.
Which type should you use?

• A.Full
• B.Member
• C.Simple
• D.None
• E.Attribute

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a Microsoft SQL server that has Data Quality Services (DQS) installed. You need to review the completeness and the uniqueness of the data stored in the matching policy. Solution: You modify the weight of the domain in the matching rule.
Does this meet the goal?

• A.No
• B.Yes

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are the administrator of a Microsoft SQL Server Master Data Services (MDS) instance. The instance contains a model named Geography and a model named Customer. The Geography model contains an entity named CountryRegion.
You need to ensure that the CountryRegion entity members are available in the customer model.
Solution: Create a CountryRegion entity in the Customer model. In the Geography model, create a subscription view and load data to the entity-based staging table that contains the CountryRegion entity.
Does this meet the goal?

• A.No
• B.Yes