We provide real 70-767 exam questions and answers braindumps in two formats. Download PDF & Practice Tests. Pass Microsoft 70-767 Exam quickly & easily. The 70-767 PDF type is available for reading and printing. You can print more and practice many times. With the help of our Microsoft 70-767 dumps pdf and vce product and material, you can easily pass the 70-767 exam.

Q71. You are installing SQL Server Data Quality Services (DQS).

You need to give specific users access to the Data Quality Server. Which SQL Server application should you use?

A. SQL Server Configuration Manager

B. SQL Server Data Tools

C. SQL Server Management Studio

D. Data Quality Client

Answer: C

Explanation:

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


Q72. You are a database developer of a Microsoft SQL Server 2016 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table. You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID.

Which Transact- SQL statement should you use?

A. CREATE TABLE Customer (SourceID int NOT NULL IDENTITY,

CustomerID int NOT NULL IDENTITY, CustomerName varchar(255) NOT NULL);

B. CREATE TABLE Customer (SourceID int NOT NULL,

CustomerID int NOT NULL PRIMARY KEY CLUSTERED,

CustomerName varchar(255) NOT NULL);

C. CREATE TABLE Customer

(SourceID int NOT NULL PRIMARY KEY CLUSTERED,

CustomerID int NOT NULL UNIQUE, CustomerName varchar(255) NOT NULL);

D. CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL,

CustomerName varchar(255) NOT NULL,

CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED

(SourceID, CustomerID));

Answer: D


Q73. You are troubleshooting an existing SQL Server Integration Services (SSIS) package. On several occasions, the package execution does not finish and no data seems to have been transferred.

You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts.

What should you do?

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

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

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

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

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

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

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

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

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

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

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

Answer: C

Explanation:

References:

http://msdn.microsoft.com/en-us/library/ms141212.aspx http://www.mssqltips.com/sqlservertip/2450/ssis-package-deployment-model-in-sql-server-2016-part-1-of-2/

http://www.mssqltips.com/sqlservertip/2450/ssis-package-deployment-model-in-sql-server-2016-part-2-of-2/


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


Q75. A SQL Server Integration Services (SSIS) package imports daily transactions from several files into a SQL Server table named Transaction. Each file corresponds to a different store and is imported in parallel with the other files. The data flow tasks use OLE DB destinations in fast load data access mode.

The number of daily transactions per store can be very large and is growing. The Transaction table does not have any indexes.

You need to minimize the package execution time. What should you do?

A. Partition the table by day and store.

B. Create a clustered index on the Transaction table.

C. Run the package in Performance mode.

D. Increase the value of the Row per Batch property.

Answer: D

Explanation: * Data Access Mode – This setting provides the 'fast load' option which internally uses a BULK INSERT statement for uploading data into the destination table instead of a simple INSERT statement (for each single row) as in the case for other options.

* BULK INSERT parameters include: ROWS_PER_BATCH =rows_per_batch

Indicates the approximate number of rows of data in the data file.

By default, all the data in the data file is sent to the server as a single transaction, and the number of rows in the batch is unknown to the query optimizer. If you specify ROWS_PER_BATCH (with a value > 0) the server uses this value to optimize the bulk- import operation. The value specified for ROWS_PER_BATCH should approximately the same as the actual number of rows.


Q76. You are creating a SQL Server Master Data Services (MDS) model for a company.

The source data for the company is stored in a single table that contains the manager-to- subordinate relationships.

You need to create a hierarchy representing the organizational structure of the company. Which hierarchy type should you use?

A. Organizational

B. Recursive

C. Non-Mandatory Explicit

D. Many-to-Many

Answer: B


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

You need to establish the correct order for loading each table to maximize parallel processing.

Which order should you use?

A. 1. DimCurrency, DimScenario, DimAccount in parallel

2. DimOrganization

3. FactFinance

B. 1. DimCurrency, DimOrganization in parallel

2. DimScenario, DimAccount in parallel

3. FactFinance

C. 1. DimCurrency, FactFinance in parallel

2. DimOrganization, DimScenario, DimAccount in parallel

D. 1. FactFinance

2. DimOrganization, DimScenario, DimAccount in parallel

3. DimCurrency

E. 1. DimCurrency

2. DimOrganization

3. DimScenario, DimAccount in parallel

4. FactFinance

Answer: A

Explanation: References: http://msdn.microsoft.com/en-us/library/ms139892.aspx http://msdn.microsoft.com/en-us/library/ms141261.aspx


Q78. You are maintaining a Data Quality Services (DQS) environment. The production server failed and a new server has been set up. The DQS databases are restored to a new server. All the appropriate permissions are granted.

DQS users are experiencing issues connecting to the new Data Quality Server. You need to enable users to connect to the new server.

Which Surface Area Configuration property should you enable?

A. AdHocRemoteQueriesEnabled

B. SoapEndpointsEnabled

C. ClrIntegrationEnabled

D. RemoteDacEnabled

E. OleAutomationEnabled

F. XpCmdShellEnabled

Answer: C


Q79. You are adding a new capability to several dozen SQL Server Integration Services (SSIS) packages.

The new capability is not available as an SSIS task. Each package must be extended with the same new capability.

You need to add the new capability to all the packages without copying the code between packages.

What should you do?

A. Use the Expression task.

B. Use the Script task.

C. Develop a custom task.

D. Use the Script component,

E. Develop a custom component.

Answer:

Explanation: References:

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


Q80. You are deploying a project to the SQL Server Integration Services (SSIS) catalog.

You need to ensure that the project is deployed to the production environment with the least amount of administrative effort.

What should you do?

A. Create an On Error event handler.

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

C. Use the gacutil command.

D. Create a reusable custom logging component.

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

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

G. Use the Integration Services Deployment Wizard.

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

I. Use the dtutil /copy command.

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

K. Deploy the package by using an msi file.

Answer: G

Explanation: 

Reference:

http://msdn.microsoft.com/en-us/library/hh479588.aspx http://msdn.microsoft.com/en-us/library/hh213290.aspx http://msdn.microsoft.com/en-us/library/hh213373.aspx