70-767 Exam
Up to the minute Microsoft 70-767 - An Overview 31 to 40
we provide Realistic Microsoft 70-767 vce which are the best for clearing 70-767 test, and to get certified by Microsoft Implementing a SQL Data Warehouse (beta). The 70-767 Questions & Answers covers all the knowledge points of the real 70-767 exam. Crack your Microsoft 70-767 Exam with latest dumps, guaranteed!
Q31. You administer a Microsoft SQL Server 2021 server that has SQL Server Integration Services (SSIS) installed.
You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables.
You need to configure the SQL Server environment to support these packages. What should you do?
A. Create SSIS configuration files for the packages.
B. Create an Integration Services catalog.
C. Install Data Quality Services.
D. Install Master Data services.
Answer: B
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
Q32. DRAG DROP
You are the administrator for a Data Quality Server. You are adding a user who must have permission to:
✑ Edit and execute a project
✑ View the activity monitoring data
This user must not be able to:
✑ Perform any kind of knowledge management
✑ Create or change a knowledge base
✑ Terminate an activity or perform administrative duties
You need to develop a Transact-SQL (T-SQL) script to meet these requirements.
What should you do? (To answer, drag the appropriate code segment or segments to the correct location or locations in the answer area.)
Answer:
Q33. You develop and deploy a SQL Server Integration Services (SSIS) package. The package is stored in the file system.
You need to execute the package without importing it to the SSIS server.
What should you use to execute the package? (Each correct answer presents a complete solution. Choose all that apply.)
A. catalog.start_package
B. dtexec
C. SQL Server Management Studio
D. SQL Server Agent
Answer: B,D
Explanation:
Ref: http://technet.microsoft.com/en-us/library/ms141708(v=sql.110).aspx
Q34. DRAG DROP
You use SQL Server Integration Services (SSIS) for extract, transformation, load (ETL) processing.
Issues concerning addresses are discovered in the data warehouse that you manage. Validation must separate the addresses into three categories:
✑ Valid addresses
✑ Autocorrected addresses
✑ Invalid addresses
You need to enhance the SSIS packages to perform address validation by using an external service.
Which transformation should you use? (To answer, drag the appropriate transformation from the list of options to the correct location or locations in the answer area.)
Answer:
Q35. DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that imports unsorted data into a data warehouse hosted on SQL Azure.
You have the following requirements:
•A destination table must contain all of the data in two source tables.
•Duplicate records must be inserted into the destination table.
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.)
Answer:
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms141703.aspx http://msdn.microsoft.com/en-us/library/ms141775.aspx http://msdn.microsoft.com/en-us/library/ms141020.aspx http://msdn.microsoft.com/en-us/library/ms140182.aspx
Q36. DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that is ready for deployment to a production server. The package contains sensitive information secured by using the EncryptSensitiveWithUserKey package protection level.
You are preparing the package for deployment by the production operations team.
You need to ensure that the production operations team can open and execute the package without re-entering the sensitive information.
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:
Q37. You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of new records are loaded into the data warehouse weekly, outside of business hours.
Most queries are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level.
You need to partition the table to maximize the performance of queries. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Partition the fact table by month, and compress each partition.
B. Partition the fact table by week.
C. Partition the fact table by year.
D. Partition the fact table by day, and compress each partition.
Answer: D
Q38. You administer a SQL Server Integration Services (SSIS) solution in the SSIS catalog. A SQL Server Agent job is used to execute a package daily with the basic logging level.
Recently, the package execution failed because of a primary key violation when the package inserted data into the destination table.
You need to identify all previous times that the package execution failed because of a primary key violation.
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: E
Q39. 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
Q40. 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