Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 181:

  You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different sources. One source is hosted in a SQL Azure database. Each source contains products for different distributors.

  Products for each distributor source must be combined for insertion into a single product table destination.

  You need to select the appropriate data flow transformation to meet this requirement.

  Which transformation types should you use? (Each correct answer presents a complete solution. Choose all that apply.)

  A. Multicast
  B. Merge Join
  C. Term Extraction
  D. union All
  E. Merge
  D. union All
  E. Merge

  ---

  Reference: http://msdn.microsoft.com/en-us/library/ms141703.aspx Reference: http://msdn.microsoft.com/en-us/library/ms141775.aspx Reference: http://msdn.microsoft.com/en-us/library/ms141020.aspx Reference: http://msdn.microsoft.com/en-us/library/ms141809.aspx Reference: http://msdn.microsoft.com/en-us/library/ms137701.aspx

• Question 182:

  HOTSPOT

  You are developing a SQL Server Integration Services (SSIS) package.

  The package control flow will contain many tasks. The tasks will execute consecutively and none will execute more than once. Certain groups of tasks will share variable and transaction scope.

  You need to group tasks together while enabling them to be collapsed and expanded as a group.

  Which item should you use from the SSIS Toolbox? (To answer, select the appropriate item in the answer area.)

  Hot Area:

  

  

• Question 183:

  You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on SQL Azure. One of the columns in the data source is named ProductCode.

  Some of the data to be loaded will reference products that need special processing logic in the data flow.

  You need to enable separate processing streams for a subset of rows based on the source product code.

  Which data flow transformation should you use?

  A. Audit
  B. Source Assistant
  C. Script Task
  D. Conditional Split
  D. Conditional Split

  ---

  http://msdn.microsoft.com/en-us/library/ms137640.aspx http://msdn.microsoft.com/en-us/library/ms141150.aspx http://msdn.microsoft.com/en-us/library/ff929138.aspx http://msdn.microsoft.com/en-us/library/ff929116.aspx

• Question 184:

  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. DQS Cleansing
  C. CDC Splitter
  D. Pivot
  C. CDC Splitter

  ---

  Explanation: 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 Ref: http://msdn.microsoft.com/en-us/library/hh758656.aspx

• Question 185:

  You have a data warehouse.

  You need to move a table named Fact.ErrorLog to a new filegroup named LowCost.

  Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order

  Select and Place:

  

  

  ---

  Step 1: Add a filegroup named LowCost to the database.

  First create a new filegroup.

  Step 2:

  The next stage is to go to the Files page in the same Properties window and add a file to the filegroup (a filegroup always contains one or more files)

  Step 3:

  To move a table to a different filegroup involves moving the table's clustered index to the new filegroup. While this may seem strange at first this is not that surprising when you remember that the leaf level of the clustered index actually

  contains the table data. Moving the clustered index can be done in a single statement using the DROP_EXISTING clause as follows (using one of the AdventureWorks2008R2 tables as an example) :

  CREATE UNIQUE CLUSTERED INDEX PK_Department_DepartmentID

  ON HumanResources.Department(DepartmentID)

  WITH (DROP_EXISTING=ON,ONLINE=ON) ON SECONDARY

  This recreates the same index but on the SECONDARY filegroup.

  References:

  http://www.sqlmatters.com/Articles/Moving%20a%20Table%20to%20a%20Different%20Filegroup.aspx

• Question 186:

  DRAG DROP

  You administer a Microsoft SQL Server database. Service accounts for SQL Agents are configured to use a local user. A Microsoft SQL Server Integration Services (SSIS) job step has been created within a SQL Server Agent job. The SSIS

  package accesses a network share when exporting data from a SQL Server database.

  When you execute the SQL Server Agent job, it fails due to a permissions failure on a share on a remote server. You need to ensure that the SQL Server Agent job can execute the SSIS package. Which four actions should you perform in

  sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 187:

  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 sections, 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 Azure SQL Data Warehouse instance that must be available six months a day for reporting.

  You need to pause the compute resources when the instance is not being used.

  Solution: You use SQL Server Management Studio (SSMS).

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  To pause a SQL Data Warehouse database, use any of these individual methods.

  Pause compute with Azure portal

  Pause compute with PowerShell

  Pause compute with REST APIs

  References:

  https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-manage- compute-overview

• Question 188:

  You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named

  ProductCode.

  Some of the data to be loaded will reference products that need special processing logic in the data flow.

  You need to enable separate processing streams for a subset of rows based on the source product code.

  Which Data Flow transformation should you use?

  A. Multicast
  B. Conditional Split
  C. Script Task
  D. Data Conversion
  B. Conditional Split

  ---

  Explanation: We use Conditional Split to split the source data into separate processing streams.

  A Script Component (Script Component is the answer to another version of this question) could be used but this is not the same as a Script Task.

• Question 189:

  You are developing a SQL Server Integration Services (SSIS) package. You need to design a package to change a variable value during package execution by using the least amount of development effort. What should you use?

  A. Expression task
  B. Script task
  C. Execute SQL task
  D. Execute Process task
  E. Term Extraction transformation
  A. Expression task

  ---

  Reference: http://msdn.microsoft.com/en-us/library/hh213137.aspx

• Question 190:

  You are preparing to install SQL Server 2016 Master Data Services (MDS).

  You need to ensure that the database requirements are met.

  What should you install?

  A. Microsoft SharePoint Foundation 2010 SP1
  B. SQL Server 2016 Enterprise (64-bit) x64 on the database server
  C. SQL Server 2016 Data Center (64-bit) x64 on the database server
  D. SQL Server 2008 Enterprise (64-bit) x64 on the database server
  B. SQL Server 2016 Enterprise (64-bit) x64 on the database server

  ---

  *

  Master Data Services is a new feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2016.

  *

  SQL Server 2016 Enterprise features include Master Data Services:

  

  Note:

  * Microsoft SQL Server Master Data Services is a Master Data Management (MDM) product from Microsoft, which will ship as a part of the Microsoft SQL Server database.Originally code-named Bulldog, Master Data Services is the rebranding of the Stratature MDM product titled +EDM, which Microsoft acquired in June 2007. Master Data Services is architecturally similar to +EDM, with increased integration with other Microsoft applications as well as some new features. Master Data Services first shipped with Microsoft SQL Server 2008 R2.