Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 251:

  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 loading data from an OLTP database to a data warehouse. The database contains a table named Sales.

  Sales contains details of records that have a type of refund and records that have a type of sales.

  The data warehouse design contains a table for sales data and a table for refund data.

  Which component should you use to load the data to the warehouse?

  A. the Slowly Changing Dimension transformation
  B. the Conditional Split transformation
  C. the Merge transformation
  D. the Data Conversion transformation
  E. an Execute SQL task
  F. the Aggregate transformation
  G. the Lookup transformation
  B. the Conditional Split transformation

  ---

  The Conditional Split transformation can route data rows to different outputs depending on the content of the data. The implementation of the Conditional Split transformation is similar to a CASE decision structure in a programming language. The transformation evaluates expressions, and based on the results, directs the data row to the specified output. This transformation also provides a default output, so that if a row matches no expression it is directed to the default output.

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

• Question 252:

  You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS).

  The package includes custom scripts that must be upgraded.

  You need to upgrade the package to SQL Server 2016.

  Which tool should you use?

  A. SQL Server Configuration Manager
  B. SQL Server Agent
  C. SSIS Upgrade Wizard in SQL Server Management Studio
  D. SQL Server DTExecUI utility (dtexecui.exe)
  C. SSIS Upgrade Wizard in SQL Server Management Studio

  ---

  Use the SSIS Package Upgrade Wizard to migrate packages that were developed in SQL Server 2005 Integration Services (SSIS) and SQL Server 2008 Integration Services (SSIS) to the package format that the current release of Integration Services uses. Reference: SSIS Upgrade Wizard (SSIS Package Upgrade Wizard)

• Question 253:

  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. You run the following Transact-SQL statement:

  

  The query fails to return results.

  You need to determine why the query fails.

  Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  To use submit_time we must use sys.dm_pdw_exec_requests table.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-pdw-exec-requests-transact-sql?view=aps-pdw-2016-au7

• Question 254:

  You are developing a Microsoft SQL Server Integration Services (SSIS) package. You enable the SSIS log provider for the Windows event log. You configure the package to use the ScriptTaskLogEntry event. You create a custom Script task.

  You need to ensure that when the script completes, it writes the execution status to the event log on the server that hosts SSIS.

  Which code segment should you add to the Script task?

  A. Dts.TaskResult = (int)ScriptResults.Failure
  B. Dts.Events.FireWarning (0, "SSIS", "Script executed with return result " Dts.TaskResult, String.Empty, 0)
  C. System.Diagnostics.EventLog.writeEntryC'SSIS", "Script executed with return result " + Dts.TaskResult, System.Diagnostics.EventLogEntryType.Information)
  D. Dts.TaskResult = (int)ScriptResults.Success
  D. Dts.TaskResult = (int)ScriptResults.Success

• Question 255:

  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 have a database named DB1 that has change data capture enabled.

  A Microsoft SQL Server Integration Services (SSIS) job runs once weekly. The job loads changes from DB1 to a data warehouse by querying the change data capture tables.

  You discover that the job loads changes from the previous three days only.

  You need re ensure that the job loads changes from the previous week.

  Which stored procedure should you execute?

  A. catalog.deploy_project
  B. catalog.restore_project
  C. catalog.stop.operation
  D. sys.sp_cdc.addJob
  E. sys.sp.cdc.changejob
  F. sys.sp_cdc_disable_db
  G. sys.sp_cdc_enable_db
  H. sys.sp_cdc.stopJob
  A. catalog.deploy_project

  ---

  catalog.deploy_project deploys a project to a folder in the Integration Services catalog or updates an existing project that has been deployed previously. References: https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-deploy-project- ssisdb-database

• Question 256:

  Your company has several line-of-business applications. The applications use a server that has SQL Server installed and contains several databases.

  You need that the business applications can access curated and validated data from the databases.

  Which features should you deploy to the server?

  A. Data Quality Services (DQS)
  B. SQL Server Analysis Services (SSAS) dimension hierarchies.
  C. SQL Server Integration Services (SSIS)
  D. Master Data Services (MDS) subscriptions.
  C. SQL Server Integration Services (SSIS)

• Question 257:

  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 a database administrator for an e-commerce company that runs an online store. The company has the databases described in the following table.

  

  Each day, data from the table OnlineOrder in DB2 must be exported by partition. The tables must not be locked during the process.

  You need to write a Microsoft SQL Server Integration Services (SSIS) package that performs the data export. What should you use?

  A. Lookup transformation
  B. Merge transformation
  C. Merge Join transformation
  D. MERGE statement
  E. Union All transformation
  F. Balanced Data Distributor transformation
  G. Sequential container
  H. Foreach Loop container
  E. Union All transformation

  ---

  The Union All transformation combines multiple inputs into one output. For example, the outputs from five different Flat File sources can be inputs to the Union All transformation and combined into one output. References: https://docs.microsoft.com/en-us/sql/integration-services/data- flow/transformations/union-all-transformation

• Question 258:

  You are designing a warehouse named DW1.

  A table named Table1 is partitioned by using the following partitioning scheme and function.

  

  Reports are generated from the data in Table1.

  You need to ensure that queries to DW1 return results as quickly as possible.

  Which column should appear in the WHERE statement clause of the query?

  A. AccountNumber
  B. MyId
  C. DueDate
  D. OrderDate
  D. OrderDate

• Question 259:

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

  The package sources data from an HTML web page that lists product stock levels. You need to implement a data flow task that reads the product stock levels from the HTML web page.

  Which data flow source should you use?

  A. Raw File source
  B. XML source
  C. Custom source component
  D. Flat File source
  C. Custom source component

• Question 260:

  You have a data warehouse named DW1. All data files are located on drive E.

  You expect queries that pivot hundreds of millions of rows for each report.

  You need to modify the data files to minimize latency.

  What should you do?

  A. Add more data files to DW1 on drive E.
  B. Add more data files to tempdb on drive E.
  C. Remove data files from tempdb
  D. Remove data files from DW1.
  B. Add more data files to tempdb on drive E.

  ---

  The number of files depends on the number of (logical) processors on the machine. As a general rule, if the number of logical processors is less than or equal to eight, use the same number of data files as logical processors. If the number of logical processors is greater than eight, use eight data files and then if contention continues, increase the number of data files by multiples of 4 until the contention is reduced to acceptable levels or make changes to the workload/code.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database