All About 70-767 practice question Oct 2021

It is more faster and easier to pass the Microsoft 70-767 exam by using Realistic Microsoft Implementing a SQL Data Warehouse (beta) questuins and answers. Immediate access to the Updated 70-767 Exam and find the same core area 70-767 questions with professionally verified answers, then PASS your exam with a high score now.

Q35. You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The package and all three Execute SQL tasks have their TransactionOption property set to Supported.

You need to ensure that if any of the Execute SQL tasks fail, all three tasks will roll back their changes.

What should you do?

A. Move the three Execute SQL tasks into a Sequence container.

B. Move the three Execute SQL tasks into a Foreach Loop container.

C. Change the TransactionOption property of all three Execute SQL tasks to Required.

D. Change the TransactionOption property of the package to Required.

Answer: D

Explanation: 

Reference:

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

us/library/microsoft.sqlserver.dts.runtime.dtstransactionoption.aspx

Q36. To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a logging methodology is put in place.

The methodology has the following requirements:

•The deployment process must be simplified.

•All the logs must be centralized in SQL Server.

•Log data must be available via reports or T-SQL.

•Log archival must be automated.

You need to configure a logging methodology that meets the requirements while minimizing the amount of deployment and development effort.

What should you do?

A. Open a command prompt and run the gacutil command.

B. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.

C. Add an OnError event handler to the SSIS project.

D. Use an msi file to deploy the package on the server.

E. Configure the output of a component in the package data flow to use a data tap.

F. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.

G. Open a command prompt and run the dtexec /rep /conn command.

H. Open a command prompt and run the dtutil /copy command.

I. Open a command prompt and run the dtexec /dumperror /conn command.

J. Configure the SSIS solution to use the Project Deployment Model.

K. Create a reusable custom logging component and use it in the SSIS project.

Answer: B

Explanation: 

Reference:

http://msdn.microsoft.com/en-us/library/ms140246.aspx http://msdn.microsoft.com/en-us/library/ms180378(v=sql.110).aspx

Q37. DRAG DROP

A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse.

The SSIS catalog includes two Environments:

✑  Development

✑ QA

Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the development or QA data warehouses.

You need to be able to execute deployed packages by using either of the defined Environments.

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.)

Answer:

Q38. You are the data steward for a Business Intelligence project.

You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.

You need to produce the CSV file with the least amount of development effort. What should you do?

A. Create an Integration Services package and use a Data Profiling transform.

B. Create a custom .NET application based on the Knowledgebase class.

C. Create a data quality project.

D. Create a CLR stored procedure based on the Knowledgebase class.

E. Create a Master Data Services (MDS) business rule.

Answer: C

Explanation: 

Reference:

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

Q39. DRAG DROP

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

The package uses custom functionality that accesses a SQL Server database. The custom functionality must be implemented by using Language Integrated Query (LINQ).

You need to ensure that the LINQ code can be debugged at design time.

What should you select from the SSIS Toolbox? (To answer, drag the appropriate item to the correct location in the answer area.)

Answer:

Q40. 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 2021. Which tool should you use?

A. SSIS Upgrade Wizard in SQL Server 2008 BIDS

B. SSIS Upgrade Wizard in SQL Server Data Tools

C. SQL Server DTExecUI utility (dtexecui.exe)

D. SQL Server dtexec utility (dtexec.exe)

Answer: B

Q41. You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of new records are loaded into the data warehouse weekly, outside of business hours.

Most queries are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level.

You need to partition the table to maximize the performance of queries. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Partition the fact table by month, and compress each partition.

B. Partition the fact table by week.

C. Partition the fact table by year.

D. Partition the fact table by day, and compress each partition.

Answer: D

Q42. 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. Data Cleansing transformation

C. Fuzzy Lookup transformation

D. Term Lookup transformation

E. Data Profiling task

Answer: A

Q43. 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

Answer: A

Explanation: 

Reference:

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

Q44. DRAG DROP

You are designing a SQL Server Integration Services (SSIS) package to execute 12 Transact-SQL (T-SQL) statements on a SQL Azure database. The T-SQL statements may be executed in any order. The T-SQL statements have unpredictable execution times.

You have the following requirements:

•The package must maximize parallel processing of the T-SQL statements.

•After all the T-SQL statements have completed, a Send Mail task must notify administrators.

You need to design the SSIS package. 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.)

Answer:

Q45. You are developing a SQL Server Integration Services (SSIS) package that imports data from a relational database to a data warehouse.

You are importing data from a relational table named Projects. The table has change data capture enabled on all columns.

You need to process only the most recent values from rows that have been inserted or updated since the previous execution of the package.

Which query should you use as the data source?

A. Option A

B. Option B

C. Option C

D. Option D

Answer: D

Explanation: 

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

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

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

Q46. You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart.

You have the following requirements:

•Ensure that the process supports the creation of an exception report that details possible duplicate key values, null ratios within columns, and column-length distributions of values.

•Ensure that users can generate the exception report in an XML format.

•Use the minimum development effort.

You need to design the SSIS solution to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Use a Data Profiling task. Use a Data Flow task to extract the XML output of the Data Profiling task into a SQL Server table. Query the table to view the exceptions.

B. Use Data Flow tasks to process the clean data.

C. Use a Data Profiling task. Read the exceptions in Data Profile Viewer.

D. Design a stored procedure that examines data for common dirty data patterns. Use an Execute SQL task.

Answer: C

Q47. DRAG DROP

You plan to deploy a SQL Server Integration Services (SSIS) project by using the project deployment model.

You need to monitor control flow tasks to determine whether any of them are running longer than usual. 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.)

Answer:

Explanation:

1. Connect to SSISDB.

2. Query against execution_component_phases and executions.

3. Execute query

Ref:

http://msdn.microsoft.com/en-us/library/hh230981.aspx http://msdn.microsoft.com/en-us/library/ff878089.aspx

Q48. HOTSPOT

You are designing a SQL Server Integration Services (SSIS) package configuration strategy.

The package configuration must meet the following requirements:

•Include multiple properties in a configuration.

•Support several packages with different configuration settings.

You need to select the appropriate configuration. Which configuration type should you use? To answer, select the appropriate option from the drop-down list in the dialog box.

Answer:

Q49. You are developing a SQL Server Integration Services (SSIS) project with multiple packages to copy data to a Windows Azure SQL Database database.

An automated process must validate all related Environment references, parameter data types, package references, and referenced assemblies. The automated process must run on a regular schedule.

You need to establish the automated validation process by using the least amount of administrative effort.

What should you do?

A. Use an event handler for OnError for the package.

B. Use an event handler for OnError for each data flow task.

C. Use an event handler for OnTaskFailed for the package.

D. View the job history for the SQL Server Agent job.

E. View the All Messages subsection of the All Executions report for the package.

F. Store the System::SourceID variable in the custom log table.

G. Store the System::ServerExecutionID variable in the custom log table.

H. Store the System::ExecutionInstanceGUID variable in the custom log table.

I. Enable the SSIS log provider for SQL Server for OnError in the package control flow.

J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.

K. Deploy the project by using dtutil.exe with the /COPY DTS option.

L. Deploy the project by using dtutil.exe with the /COPY SQL option.

M. Deploy the .ispac file by using the Integration Services Deployment Wizard.

N. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.

O. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.

P. Create a SQL Server Agent job to execute the

SSISDB.catalog.create_execution and SSISDB.catalog.start_execution stored procedures.

Q. Create a table to store error information. Create an error output on each data flow destination that writes OnError event text to the table.

R. Create a table to store error information. Create an error output on each data flow destination that writes OnTaskFailed event text to the table.

Answer: N

Q50. DRAG DROP

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

The package uses custom functionality that accesses a SQL Server database. The custom functionality must be implemented by using Language Integrated Query (LINQ).

You need to ensure that the LINQ code can be debugged at design time.

What should you select from the SSIS Toolbox? (To answer, drag the appropriate item to the correct location in the answer area.)

Answer:

Q51. You are reviewing the design of an existing fact table named factSales, which is loaded incrementally from a SQL Azure database by a SQL Server Integration Services (SSIS) package each hour. The fact table has approximately 4 billion rows and is dimensioned by product, sales date, and sales time of day.

The database administrator is concerned about the rapid growth of the database and users experience poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses this fact table reports sales by product name, sale month, and sale year. No other reports will be created against this table.

You need to reduce the report processing time and minimize the growth of the database. What should you do?

A. Create an indexed view over the fact table to sum orderTotal by month.

B. Create a view over the fact table to sum orderTotal by month.

C. Change the granularity of the fact table to month.

D. Partition the fact table by productKey.

Answer: C