Mobile Menu

[Free] 2019(Nov) EnsurePass Microsoft 70-767 Dumps with VCE and PDF 101-110

by admin, November 7, 2019

Get Full Version of the Exam
http://www.EnsurePass.com/70-767.html

Question No.101

You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse hosted on SQL Azure.

The package uses a Foreach container to process text files found in a folder. The package must be deployed to a single server by using the Project Deployment model.

Multiple SQL Server Agent jobs call the package. Each job is executed on a different schedule. Each job passes a different folder path to the package.

You need to configure the package to accept the folder path from each job. Which package configuration should you use?

  1. Parent Package Variable

  2. XML Configuration File

  3. Environment Variable

  4. .dtsConfig file

  5. Registry Entry

Correct Answer: C

Question No.102

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?

  1. Audit

  2. Source Assistant

  3. Script Task

  4. Conditional Split

Correct 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

Question No.103

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?

  1. Convert the parameters to package parameters.

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

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

  4. Convert the project to the Legacy Deployment model.

Correct Answer: B

Question No.104

You install a SQL Server 2016 database engine instance on a production server. A month later, you install SQL Server 2016 Integration Services (SSIS).

You must develop an SSIS project and deploy it to the server by using the Project Deployment model.

Operations log records that are outside the configured retention period must be cleaned automatically.

You need to create the SSIS catalog on the production server and ensure that the operations log cleaning requirement is met.

What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

  1. Enable CLR Integration.

  2. Enable FILESTREAM with Full Access.

  3. Enable the Resource Governor.

  4. Change the recovery mode of the msdb database to FULL.

  5. Change the Server-wide Default Logging Level in SSISDB to Verbose.

  6. Start the SQL Server Browser service.

  7. Start the SQL Server Agent service.

Correct Answer: AG

Question No.105

DRAG DROP

You are developing a SQL Server Integration Services (SSIS) package that loads data into a data warehouse hosted on Windows Azure SQL Database.

You must combine two data sources together by using the ProductID column to provide complete details for each record. The data retrieved from each data source is sorted in ascending order by the ProductID column.

You need to develop a data flow that imports the data while meeting the requirements. How should you develop the data flow?

To answer, drag the appropriate transformation from the list of transformations to the correct location in the answer area.

image

Correct Answer:

image

Question No.106

You are developing a SQL Server Integration Services (SSIS) package to load data into a Windows Azure SQL Database database. The package consists of several data flow tasks.

The package has the following auditing requirements:

image

If a data flow task fails, a Transact-SQL (T-SQL) script must be executed.

image

The T-SQL script must be executed only once per data flow task that fails, regardless of the nature of the error.

You need to ensure that auditing is configured to meet these requirements. What should you do?

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

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

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

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

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

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

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

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

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

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

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

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

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

  14. Create a SQL Server Agent job to execute the SSISDB.catalog.va!idate_project stored procedure.

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

  16. Create a SQL Server Agent job to execute the SSISDB.catalog.create_execution and SSISDB.catalog.start_execution stored procedures.

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

Correct Answer: C

Question No.107

You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure database into a data warehouse.

The source system contains redundant or inconsistent data. When the package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a text file for further analysis.

You need to establish the best technique to log these invalid rows while keeping the amount of development effort to a minimum.

What should you do?

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

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

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

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

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

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

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

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

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

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

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

Correct Answer: I

Explanation:

http://technet.microsoft.com/en-us/library/hh230989.aspx

http://www.rafael-salas.com/2016/01/ssis-2016-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/ms162820.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx http://technet.microsoft.com/en-us/library/ms140223.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx

Question No.108

You are deploying a new SQL Server Integration Services (SSIS) package to several servers. The package must meet the following requirements:

image

NET Common Language Runtime (CLR) integration in SQL Server must not be enabled.

image

The Connection Managers used in the package must be configurable without editing the package.

image

The deployment procedure must be automated as much as possible.

You need to set up a deployment strategy that meets the requirements. What should you do?

  1. Use the gacutil command.

  2. Use the dtutil /copy command.

  3. Use the Project Deployment Wizard.

  4. Create an OnError event handler.

  5. Create a reusable custom logging component.

  6. Run the package by using the dtexec /rep /conn command.

  7. Run the package by using the dtexec /dumperror /conn command.

  8. Run the package by using the dtexecui.exe utility and the SQL Log provider.

  9. Add a data tap on the output of a component in the package data flow.

  10. Deploy the package by using an msi file.

  11. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.

Correct Answer: B

Question No.109

DRAG DROP

You are creating a SQL Server Integration Services (SSIS) package to populate a fact table from a source table. The fact table and source table are located in a SQL Azure database. The source table has a price field and a tax field. The OLE DB source uses the data access mode of Table.

You have the following requirements:

image

The fact table must populate a column named TotalCost that computes the sum of the price and tax columns.

image

Before the sum is calculated, any records that have a price of zero must be discarded. You need to create the SSIS package in SQL Server Data Tools.

In what sequence should you order four of the listed components for the data flow task?

To answer, move the appropriate components from the list of components to the answer area and arrange them in the correct order.

image

Correct Answer:

image

Question No.110

DRAG DROP

A new dedicated server is used to execute resource-intensive SQL Server Integration Services (SSIS) 2016 packages. The environment that you are deploying the packages to has the following constraints:

image

image

The operating system is Windows Server 2008 R2. The SSIS packages are stored in the SSIS catalog.

image

Some of these SSIS packages use 32-bit custom components.

You need to install only the components that are required to deploy and run the packages on the new server.

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.

image

Correct Answer:

image

Get Full Version of the Exam
70-767 Dumps
70-767 VCE and PDF

Categories