[2021-New] Microsoft 70-767 Dumps With Update Exam Questions (51-60)

70-767 Dumps

70-767 Exam Questions - Online Test

70-767 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Q1. DRAG DROP

You are developing a SQL Server Integration Services (SSIS) package that downloads data from a Windows Azure SQL Database database.

A stored procedure will be called in an Execute SQL task by using an ODBC connection. This stored procedure has only the @CustomerID parameter of type INT.

A project parameter named CustID will be mapped to the stored procedure parameter

@CustomerID.

You need to ensure that the value of the CustID parameter is passed to the @CustomerID stored procedure parameter.

In the Parameter Mapping tab of the Execute SQL task editor, how should you configure the parameter? (To answer, drag the appropriate option or options to the correct location or locations in the answer area.)

Answer:

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

Answer: D

Explanation:

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

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

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

Q5. DRAG DROP

You are designing a SQL Server Integration Services (SSIS) package. The package moves order-related data to a staging table named Order. Every night the staging data is truncated and then all the recent orders from the online store database are inserted into the staging table.

Your package must meet the following requirements:

•If the truncate operation fails, the package execution must stop and report an error.

•If the Data Flow task that moves the data to the staging table fails, the entire refresh operation must be rolled back.

•For auditing purposes, a log entry must be entered in a SQL log table after each execution of the Data Flow task.

The TransactionOption property for the package is set to Required. You need to design the package to meet the requirements.

How should you design the control flow for the package? (To answer, drag the appropriate setting from the list of settings to the correct location or locations in the answer area.)

Answer:

Explanation:

References:

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

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

Q6. You are developing a project that contains multiple SQL Server Integration Services (SSIS) packages. The packages will be deployed to the SSIS catalog. One of the steps in each package accesses an FTP site to download data files.

You create project parameters to store the username and password that are used to access the FTP site.

You need to ensure that the username and password values are encrypted when they are deployed.

What should you do?

A. Convert the parameters to package parameters.

B. Set the Sensitive property of the parameters to True.

C. Set the ProtectionLevel property of the package to EncryptSensitiveWithPassword.

D. Convert the project to the Legacy Deployment model.

Answer: B

Q7. You are designing a SQL Server Integration Services (SS1S) package that uploads a file to a table named Orders in a SQL Azure database.

The company's auditing policies have the following requirements:

•An entry must be written to a dedicated SQL Server log table named OrderLog.

•The entry must be written as soon as the file upload task completes.

You need to meet the company's policy requirements. Which event handler should you use?

A. OnProgress

B. Onlnformation

C. OnPostExecute

D. OnComplete

Answer: C

Explanation:

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

Q8. DRAG DROP

You are building a SQL Server Integration Services (SSIS) package to load product data sourced from a SQL Azure database to a data warehouse. Before the product data is loaded, you create a batch record by using an Execute SQL task named Create Batch. After successfully loading the product data, you use another Execute SQL task named Set Batch Success to mark the batch as successful.

You need to create and execute an Execute SQL task to mark the batch as failed if either the Create Batch or Load Products task fails.

Which three steps 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:

References:

http://msdn.microsoft.com/en-us/library/ms141003.aspx http://msdn.microsoft.com/en-us/library/ms141261.aspx http://msdn.microsoft.com/en-us/library/ms141722.aspx http://msdn.microsoft.com/en-us/library/ms139895.aspx

Q9. DRAG DROP

You are loading a dataset into SQL Server. The dataset contains numerous duplicates for the Artist and Song columns.

The values in the Artist column in the dataset must exactly match the values in the Artist domain in the knowledge base. The values in the Song column in the dataset can be a close match with the values in the Song domain.

You need to use SQL Server Data Quality Services (DQS) to define a matching policy rule to identify duplicates.

How should you configure the Rule Editor? (To answer, drag the appropriate answers to the answer area.)

Answer:

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

Answer: D,E

Explanation:

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