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

Get Full Version of the Exam

Question No.181

You have a server named SQL1 that has SQL Server Integration Services (SSIS) installed. SQL1 has eight CPUs, 8 GB of RAM, RAID-1 storage, and a 10-gigabit Ethernet connection.

A package named Package1 runs on SQL1. Package1 contains 10 data tasks. Package1 queries 3 GB of data from the source system.

You review the performance statistics on SQL1 and discover that Package1 executes slower than expected.

You need to identify the problem that is causing package1 to execute slowly. Which performance monitor counter data should you review?

  1. SQLServer:SSIS Pipeline 10.0:Buffers in use

  2. Processor\% Idle Time

  3. SQLServer:SSIS Pipeline 10.0:Buffers spooled

  4. SQLServer:SSIS Pipeline 10.0:Private buffer memory

Correct Answer: B

Question No.182


You deploy a server that has SQL Server installed.

You deploy a SQL Server Integration Services (SSIS) package to the server.

You need to automate the execution of the package. The solution must ensure that you receive a notification if the package to execute.

In which order should you perform all the actions?


Correct Answer:


Question No.183


You are the data steward at your company.

Duplicate customers exist in a Microsoft Excel workbook. You create a Data Quality Services (DQS) knowledge base and matching policy to identify these duplicate customers.

You need to identify the duplicate customers. Which option should you use?

To answer, select the appropriate option in the answer area.


Correct Answer:


Question No.184

You are administering SQL Server Integration Services (SSIS) permissions on a production

server that runs SQL Server 2016.

Package developers in your company must have permission to perform the following tasks only on their own projects:

View projects and packages, View Environments, Validate packages, Execute packages. You need to grant rights to the developers without assigning unnecessary privileges.

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

  1. Add developer logins to the db_ssisltduser role in the msdb database.

  2. Add developer logins to the db_ssisoperator role in the msdb database.

  3. Grant Execute permission in the projects for the developer logins.

  4. Grant Read permission in the SSIS catalog folder, the projects, and the Environments.

  5. Add developer logins to the ssis_admin role in the SSISDB database.

  6. Grant Modify permission in the projects for the developer logins.

Correct Answer: BD


B: db_ssisoperator Read actions

Enumerate all packages. View all packages.

Execute all packages. Export all packages.

Execute all packages in SQL Server Agent. Write actions


D: Need read permissions on the Environments in order to be able to view them. Incorrect:

Not A: db_ssisltduser Read actions

Enumerate own packages. Enumerate all packages. View own packages.

Execute own packages. Export own packages. Write Actions

Import packages. Delete own packages.

Change own package roles. Not E: db_ssisadmin

Too many permissions (such as delete all packages).

Question No.185

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?

  1. DataReader

  2. OLE DB

  3. ADO.NET

  4. SQL Server

Correct Answer: B

Question No.186

You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS). You need to complete the post-installation configuration. What should you do?

  1. Install the Analysis Services OLE DB Provider.

  2. Run the DQSInstaller.exe command.

  3. Run the Configuration component in the Data Quality Client.

  4. Make the data available for DQS operations.

Correct Answer: B

Question No.187

You are implementing a SQL Server Integration Services (SSIS) 2016 package that loads data from various flat files and a Windows Azure SQL Database database.

Daily transactions must be loaded into a staging database. All the SSIS tasks will use the CurrentDate variable as the transaction date.

You need to set the CurrentDate variable to the date stored in a control table of the Windows Azure SQL Database database when the package starts. You need to achieve this goal by using the least amount of development effort.

What should you use to set the variable?

  1. an Expression task

  2. an Execute SQL task

  3. a Script component

  4. a Script task

Correct Answer: B

Question No.188

You are designing a data warehouse hosted on Windows Azure SQL Database. The data warehouse currently includes the dimUser and dimRegion dimension tables and the factSales fact table. The dimUser table contains records for each user permitted to run reports against the

warehouse, and the dimRegion table contains information about sales regions.

The system is accessed by users from certain regions, as well as by area supervisors and users from the corporate headquarters.

You need to design a table structure to ensure that certain users can see sales data for only certain regions. Some users must be permitted to see sales data from multiple regions.

What should you do?

  1. For each region, create a view of the factSales table that includes a WHERE clause for the region.

  2. Create a userRegion table that contains primary key columns from the dimUser and dimRegion tables.

  3. Add a region column to the dimUser table.

  4. Partition the factSales table on the region column.

Correct Answer: B

Question No.189


You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server 2016 database.

The package is allowed to connect to only one database. An Environment variable contains the name of the database.

The OLE DB project connection manager has been parameterized.

You need to configure the connection manager property to accept the value of the Environment variable.

Which property should you use?

To answer, configure the appropriate option or options in the dialog box in the answer area.


Correct Answer:


Question No.190

You develop a SQL Server Integration Services (SSIS) package in a project by using the Project Deployment Model. It is regularly executed within a multi-step SQL Server Agent job.

You make changes to the package that should improve performance.

You need to establish if there is a trend in the durations of the next 10 successful executions of the package. You need to use the least amount of administrative effort to achieve this goal.

What should you do?

  1. After 10 executions, view the job history for the SQL Server Agent job.

  2. After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the project.

  3. Enable logging to the Application Event Log in the package control flow for the OnInformation event. After 10 executions, view the Application Event Log.

  4. Enable logging to an XML file in the package control flow for the OnPostExecute event. After 10 executions, view the XML file.

Correct Answer: B


The All Executions Report displays a summary of all Integration Services executions that have been performed on the server. There can be multiple executions of the sample package. Unlike the Integration Services Dashboard report, you can configure the All Executions report to show executions that have started during a range of dates. The dates can span multiple days, months, or years.

The report displays the following sections of information. Filter

Shows the current filter applied to the report, such as the Start time range. Execution Information

Shows the start time, end time, and duration for each package execution.You can view a list of the parameter values that were used with a package execution, such as values that were passed to a child package using the Execute Package task.

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