Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 21:

  You have Microsoft Azure SQL Database instances named DB1 and DB2.

  The databases are hosted in different regions and contain different tables.

  You must allow Transact-SQL queries in DB1 to access data in DB2.

  You need to configure DB1 to ensure that you can run queries that join tables from DB1 and DB2.

  What should you do?

  A. Use the CREATE EXTERNAL DATA SOURCE statement to form an external data source for DB1. The LOCATION statement must reference the URL for DB2.
  B. Configure a linked server connection on DB1 to access DB2 remotely. Configure the linked server RPC Out option to TRUE.
  C. Create an elastic database pool. Place DB1 and DB2 into the elastic database pool.
  D. Create a global shard map on DB1 for horizontal partitioning. Add DB1 and DB2 to the shard map.
  B. Configure a linked server connection on DB1 to access DB2 remotely. Configure the linked server RPC Out option to TRUE.

  ---

  References: https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine?view=sql-server-ver15

• Question 22:

  A SQL Server Integration Services (SSIS) package was deployed two weeks ago with the Project Deployment Model.

  Sometimes the package is started as part of a multistep SQL job. At other times, the package is started manually by a database administrator by using the Object Explorer in SQL Server Management Studio.

  You need to identify the authenticated user responsible for starting the package each time it executes.

  How can you find this information?

  A. In the SSISDB.[catalog], query the .[executions] view.
  B. In the SSISDB.[catalog] , query the [event_messages] view.
  C. In SQL Server Management Studio, view the SQL Agent Job History.
  D. In SQL Server Management Studio, view the SQL Agent Error Log.
  E. In SQL Server Management Studio, view the SQL Server Log.
  A. In the SSISDB.[catalog], query the .[executions] view.

• Question 23:

  You have a series of analytic data models and reports that provide insights into the participation rates for sports at different schools. Users enter information about sports and participants into a client application. The application stores this transactional data in a Microsoft SQL Server database. A SQL Server Integration Services (SSIS) package loads the data into the models.

  When users enter data, they do not consistently apply the correct names for the sports.

  The following table shows examples of the data entry issues.

  

  You need to create a new knowledge base to improve the quality of the sport name data.

  How should you configure the knowledge base? To answer, select the appropriate options in the dialog box in the answer area.

  Hot Area:

  

  

  ---

  Spot 1: Create Knowledge base from: None

  Select None if you do not want to base the new knowledge base on an existing knowledge base or data file.

• Question 24:

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

  The package is stored as the myPackage.dtsx file in the root directory of the C drive of the SSIS server.

  You need to run the package from the command prompt.

  Which command should you use?

  A. dtexec /sql myPackage
  B. dtexec /d "C:\File System\myPackage.dtsx"
  C. dtexec /f "C:\myPackage.dtsx"
  D. dtexec /com "myPackage.dtsx"
  C. dtexec /f "C:\myPackage.dtsx"

• Question 25:

  You are designing a SQL Server Integration Services (SSIS) 2016 package that imports data from a Windows Azure SQL Database database into a SQL Server database. The SSIS package has the following requirements:

  

  Every night, a very large amount of data is imported into the staging database.

  

  Package processing time must be minimized.

  

  The package must run on its own dedicated server when it is deployed to production.

  

  Transaction log activity when data is imported must be minimized.

  You need to design the package to meet the requirements.

  Which destination component should you use?

  A. DataReader
  B. OLE DB
  C. ADO.NET
  D. SQL Server
  B. OLE DB

• Question 26:

  You are testing a Microsoft SQL Server Integration Services (SSIS) package. The package includes the Control Flow task shown in the Control Flow exhibit (Click the Exhibit button) and the Data Flow task shown in the Data Flow exhibit. (Click the Exhibit button.)

  

  You declare a variable named Seed as shown in the Variables exhibit. (Click the Exhibit button.) The variable is changed by the Script task during execution. You declare a variable named Seed as shown in the Variables exhibit. (Click the Exhibit button.) The variable is changed by the Script task during execution.

  You need to be able to interrogate the value of the Seed variable after the Script task completes execution. For each of the following statements, select Yes if the statement is true. Otherwise, select No.

  Hot Area:

  

  

  ---

  References: https://docs.microsoft.com/en-us/sql/integration-services/variables-window

• Question 27:

  You are developing a data flow transformation to merge two data sources. One source contains product data and the other source contains data about the country in which the product was manufactured. Both data sources contain a two-character CountryCode column and both use SQL Server. Both data sources contain an ORDER BY clause to sort the data by the CountryCode column in ascending order.

  You use a Merge Join transformation to join the data.

  

  You need to ensure that the Merge Join transformation works correctly without additional transformations. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

  A. Change the ORDER BY clause on the product source to order by ProductName.
  B. Change the Merge Join transformation to a Merge transformation.
  C. Set the appropriate SortKeyPosition properties on the data sources.
  D. Set the IsSorted property on both data sources.
  C. Set the appropriate SortKeyPosition properties on the data sources.
  D. Set the IsSorted property on both data sources.

• Question 28:

  Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series.

  Information and details provided in a question apply only to that question.

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

  You need to ensure that the packa

  ge records the current Log Sequence Number (LSN) in the source database before the package begins reading source tables.

  Which SSIS Toolbox item should you use?

  A. CDC Control task
  B. CDC Splitter
  C. Union All
  D. XML task
  E. Fuzzy Grouping
  F. Merge
  G. Merge Join
  A. CDC Control task

• Question 29:

  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.

  Your company uses Microsoft SQL Server to deploy a data warehouse to an environment that has a SQL Server Analysis Services (SSAS) instance. The data warehouse includes the Fact.Order table as shown in the following table definition. The table has no indexes.

  

  You must minimize the amount of space that indexes for the Fact.Order table consume. You run the following queries frequently. Both queries must be able to use a columnstore index:

  

  You need to ensure that the queries complete as quickly as possible.

  SolutionvYou create two nonclustered indexes. The first includes the [Order Date Key] and [Tax Amount] columns. The second will include the [Order Date Key] and [Total Excluding Tax] columns.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 30:

  You are designing a data transformation process using Microsoft SQL Server Integration Services (SSIS).

  You need to ensure that every row is compared with every other row during transformation.

  What should you configure? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  When you configure the Fuzzy Grouping transformation, you can specify the comparison algorithm that the transformation uses to compare rows in the transformation input. If you set the Exhaustive property to true, the transformation compares every row in the input to every other row in the input. This comparison algorithm may produce more accurate results, but it is likely to make the transformation perform more slowly unless the number of rows in the input is small.

  References: https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/fuzzy-grouping-transformation