70-767 Exam
Tips to Pass 70-767 Exam (51 to 60)
Our pass rate is high to 98.9% and the similarity percentage between our 70-767 study guide and real exam is 90% based on our seven-year educating experience. Do you want achievements in the Microsoft 70-767 exam in just one try? I am currently studying for the Microsoft 70-767 exam. Latest Microsoft 70-767 Test exam practice questions and answers, Try Microsoft 70-767 Brain Dumps First.
Q51. DRAG DROP
A Data Flow task in a SQL Server Integration Services (SSIS) package produces run-time errors.
You need to edit the package to log specific error messages.
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:
Q52. You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The package and all three Execute SQL tasks have their TransactionOption property set to Supported.
You need to ensure that if any of the Execute SQL tasks fail, all three tasks will roll back their changes.
What should you do?
A. Move the three Execute SQL tasks into a Sequence container.
B. Move the three Execute SQL tasks into a Foreach Loop container.
C. Change the TransactionOption property of all three Execute SQL tasks to Required.
D. Change the TransactionOption property of the package to Required.
Answer: D
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/ms137749.aspx http://msdn.microsoft.com/en-
us/library/microsoft.sqlserver.dts.runtime.dtstransactionoption.aspx
Q53. You are designing a data warehouse hosted on SQL Azure. The data warehouse currently includes the dimUser and dimDistrict dimension tables and the factSales fact table. The dimUser table contains records for each user permitted to run reports against the warehouse; and the dimDistrict table contains information about sales districts.
The system is accessed by users from certain districts, 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 districts. Some users must be permitted to see sales data from multiple districts.
What should you do?
A. Add a district column to the dimUser table.
B. Partition the factSales table on the district column.
C. Create a userDistrict table that contains primary key columns from the dimUser and dimDistrict tables.
D. For each district, create a view of the factSales table that includes a WHERE clause for the district.
Answer: C
Q54. HOTSPOT
You are developing a SQL Server Integration Service (SSIS) package. The package loads a customer dimension table by using a data flow task.
Changes to the customer attributes must be tracked over time.
You need to produce a checksum value to identify the rows that have changed since the last Extract, Transform and Load (ETL) process execution. You need to use the least amount of development effort to achieve this goal.
Which transformation should you use? (To answer, select the appropriate transformation in the answer area.)
Answer:
Q55. You are developing a SQL Server Integration Services (SSIS) project to read and write data from a Windows Azure SQL Database database to a server that runs SQL Server 2021.
The connection will be used by data flow tasks in multiple SSIS packages. The address of the target Windows Azure SQL Database database will be provided by a project parameter.
You need to create a solution to meet the requirements by using the least amount of administrative effort and maximizing data flow performance.
What should you do?
A. Use an SSIS Script task that uses the custom assembly to parse the text data when inserting it.
B. Use an SSIS Script transformation that uses the custom assembly to parse the text data when inserting it.
C. Create a SQL Common Language Runtime (SQLCLR) function that uses the custom assembly to parse the text data, deploy it in the Windows Azure SQL Database database, and use it when inserting data.
D. Create a SQL Common Language Runtime (SQLCLR) stored procedure that uses the custom assembly to parse the text data, deploy it in the Windows Azure SQL Database database, and use it when inserting data.
Answer: A
Q56. DRAG DROP
You are designing an extract, transform, load (ETL) process with SQL Server Integration Services (SSIS). Two packages, Package A and Package B, will be designed. Package A will execute Package B.
Both packages must reference a file path corresponding to an input folder where files will be located for further processing.
You need to design a solution so that the file path can be easily configured with the least administrative and development effort.
Which four 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:
Q57. You are developing a SQL Server Integration Services (SSIS) project that copies a large amount of rows from a SQL Azure database. The project uses the Package Deployment Model. This project is deployed to SQL Server on a test server.
You need to ensure that the project is deployed to the SSIS catalog on the production server.
What should you do?
A. Open a command prompt and run the dtexec /dumperror /conn command.
B. Create a reusable custom logging component and use it in the SSIS project.
C. Open a command prompt and run the gacutil command.
D. Add an OnError event handler to the SSIS project.
E. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
F. Open a command prompt and run the dtexec /rep /conn command.
G. Open a command prompt and run the dtutil /copy command.
H. Use an msi file to deploy the package on the server.
I. Configure the SSIS solution to use the Project Deployment Model.
J. Configure the output of a component in the package data flow to use a data tap.
K. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
Answer: I
Explanation: References: http://msdn.microsoft.com/en-us/library/hh231102.aspx http://msdn.microsoft.com/en-us/library/hh213290.aspx http://msdn.microsoft.com/en-us/library/hh213373.aspx
Q58. You are a database developer of a Microsoft SQL Server 2021 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
Q59. DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse.
You are developing the part of the SSIS package that populates the ProjectDates dimension table. The business key of the ProjectDates table is the ProjectName column.
The business user has given you the dimensional attribute behavior for each of the four columns in the ProjectDates table:
•ExpectedStartDate - New values should be tracked over time.
•ActualStartDate - New values should not be accepted.
•ExpectedEndDate - New values should replace existing values.
•ActualEndDate - New values should be tracked over time. You use the SSIS Slowly Changing Dimension Transformation.
You must configure the Change Type value for each source column.
Which Change Type values should you select? (To answer, drag the appropriate value from the list of values to the correct location or locations in the answer area.)
Answer:
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms141715.aspx http://msdn.microsoft.com/en-us/library/ms141662.aspx
Q60. DRAG DROP
You plan to deploy a SQL Server Integration Services (SSIS) project by using the project deployment model.
You need to monitor control flow tasks to determine whether any of them are running longer than usual. 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. Connect to SSISDB.
2. Query against execution_component_phases and executions.
3. Execute query
Ref:
http://msdn.microsoft.com/en-us/library/hh230981.aspx http://msdn.microsoft.com/en-us/library/ff878089.aspx