70-767 Exam
Shortcuts To 70-767（11 to 20）
Proper study guides for Rebirth Microsoft Implementing a SQL Data Warehouse (beta) certified begins with Microsoft 70-767 preparation products which designed to deliver the 100% Correct 70-767 questions by making you pass the 70-767 test at your first time. Try the free 70-767 demo right now.
Q11. 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
Q12. You develop a SQL Server Integration Services (SSIS) package that imports SQL Azure data into a data warehouse every night.
The SQL Azure data contains many misspellings and variations of abbreviations. To import the data, a developer used the Fuzzy Lookup transformation to choose the closest- matching string from a reference table of allowed values. The number of rows in the reference table is very large.
If no acceptable match is found, the Fuzzy Lookup transformation passes a null value. The current setting for the Fuzzy Lookup similarity threshold is 0.50.
Many values are incorrectly matched.
You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading performance.
What should you do?
A. Decrease the maximum number of matches per lookup.
B. Change the similarity threshold to 0.55.
C. Change the Exhaustive property to True.
D. Increase the maximum number of matches per lookup.
Answer: B
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms137786.aspx
Q13. 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
Q14. DRAG DROP
You develop a SQL Server Integration Services (SSIS) project by using the Project Deployment model.
The project contains many packages. It is deployed on a server named Development1. The project will be deployed to several servers that run SQL Server 2021.
The project accepts one required parameter. The data type of the parameter is a string.
A SQL Agent job is created that will call the master.dtsx package in the project. A job step is created for the SSIS package.
The job must pass the value of an SSIS Environment Variable to the project parameter. The value of the Environment Variable must be configured differently on each server that
runs SQL Server. The value of the Environment Variable must provide the server name to the project parameter.
You need to configure SSIS on the Development1 server to pass the Environment Variable to the package.
Which four actions should you perform in sequence by using SQL Server Management Studio? (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/hh479588.aspx http://msdn.microsoft.com/en-us/library/hh213230.aspx http://msdn.microsoft.com/en-us/library/hh213214.aspx http://sqlblog.com/blogs/jamie_thomson/archive/2010/11/13/ssis-server-catalogs-environments-environment-variables-in-ssis-in-denali.aspx
Q15. DRAG DROP
You are validating whether a SQL Server Integration Services (SSIS) package named Master.dtsx in the SSIS catalog is executing correctly.
You need to display the number of rows in each buffer passed between each data flow component of the 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:
Explanation:
1. Run with verbose settings
2. Get Execution ID from .executions
3. Get stats from .execution_data_statistics ( rows_sent ) Ref: http://msdn.microsoft.com/en-us/library/hh230986.aspx
Q16. HOTSPOT
A SQL Server Integration Services (SSIS) package is designed to download data from a financial database hosted in SQL Azure.
The connection string to the financial database is defined as a project parameter named FinConStr. The parameter value must be stored securely and must be set explicitly every time the package is executed.
You need to configure the parameter to meet the requirements.
What should you do? (To answer, configure the appropriate option or options in the dialog box in the answer area.)
Answer:
Q17. 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
Q18. DRAG DROP
A new dedicated server is used to execute resource-intensive SQL Server Integration Services (SSIS) 2021 packages. The environment that you are deploying the packages to has the following constraints:
✑ The operating system is Windows Server 2008 R2.
✑ The SSIS packages are stored in the SSIS catalog.
✑ 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.)
Answer:
Explanation:
Note: Step 1:
Step 2:
If you require 32-bit versions of the Integration Services runtime and tools to run certain packages in 32-bit mode, you must also install SQL Server Data Tools (SSDT).
Note:
For a complete installation of Integration Services, together with the tools and documentation for developing and managing packages, select both Integration Services and the following Shared Features:
* SQL Server Data Tools to install the tools for designing packages.
* Management Tools - Complete to install SQL Server Management Studio for managing packages.
* Client Tools SDK to install managed assemblies for Integration Services programming.
Step 3: How to Modify the Service Configuration File to Connect to a Named Instance of SQL Server
To connect to a named instance of SQL Server, you must modify the service configuration file, MsDtsSrvr.ini.xml.
Q19. 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?
A. Run the DQSInstaller.exe command.
B. Install the data providers that are used for data refresh.
C. Install ADOMD.NET.
D. Run the dbimpexp.exe command.
Answer: A
Explanation: References:
http://msdn.microsoft.com/en-us/library/ff877917.aspx http://msdn.microsoft.com/en-us/library/gg492277.aspx
Q20. HOTSPOT
You are editing a SQL Server Integration Services (SSIS) project named Project1 in SQL Server Data Tools.
A package Connection Manager has been parameterized with project scope.
You need to display the parameters that have been generated for the Connection Manager.
What should you use? To answer, select the appropriate setting or settings in the answer area.
Answer: