70-767 Exam
Top Avant-garde 70-767 practice question Reviews!
Your success in Microsoft 70-767 is our sole target and we develop all our 70-767 braindumps in a way that facilitates the attainment of this target. Not only is our 70-767 study material the best you can find, it is also the most detailed and the most updated. 70-767 Practice Exams for Microsoft MCSA 70-767 are written to the highest standards of technical accuracy.
Q1. You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model. All packages in the project must log custom messages.
You need to produce reports that combine the custom log messages with the system- generated log messages. 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: G
Q2. You are developing a project that contains multiple SQL Server Integration Services (SSIS) packages. The packages will be deployed to the SSIS catalog. One of the steps in each package accesses an FTP site to download data files.
You create project parameters to store the username and password that are used to access the FTP site.
You need to ensure that the username and password values are encrypted when they are deployed.
What should you do?
A. Convert the parameters to package parameters.
B. Set the Sensitive property of the parameters to True.
C. Set the ProtectionLevel property of the package to EncryptSensitiveWithPassword.
D. Convert the project to the Legacy Deployment model.
Answer: B
Q3. You are reviewing the design of a student dimension table in an existing data warehouse hosted on SQL Azure.
The current dimension design does not allow the retention of historical changes to student attributes such as ParentOccupation.
You need to redesign the dimension to enable the full historical reporting of changes to multiple student attributes including ParentOccupation.
What should you do?
A. Add CurrentValue and PreviousValue columns to the student dimension.
B. Enable Snapshot Isolation on the data warehouse.
C. Add an IsCurrent column to the student dimension.
D. Add StartDate and EndDate columns to the student dimension.
Answer: D
Explanation: Adding a start and end date will give you this ability as when a record is inserted and given a start and end date, you’ll have the ability to determine when they were active therefore giving you a retention of historical changes
Q4. You are using a SQL Server Integration Services (SSIS) project that is stored in the SSIS catalog. An Environment has been defined in the SSIS catalog.
You need to add the Environment to the project. Which stored procedure should you use?
A. catalog.create_environment_variable
B. catalog.create_environment_reference
C. catalog.set_execution_parameter_value
D. catalog.set_environment_variable_value
Answer: B
Explanation:
Environments (Test, Production etc) are associated with projects by creating references to the environments in the projects.
Q5. You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day.
Referential integrity must be enforced declaratively.
You need to design a solution that can join a single time dimension to both fact tables. What should you do?
A. Create a time mapping table.
B. Change the level of granularity in both fact tables to be the same.
C. Merge the fact tables.
D. Create a view on the sales table.
Answer: B
Q6. You are reviewing the design of a customer dimension table in an existing data warehouse hosted on SQL Azure.
The current dimension design does not allow the retention of historical changes to customer attributes such as Postcode.
You need to redesign the dimension to enable the full historical reporting of changes to multiple customer attributes including Postcode.
What should you do?
A. Add StartDate and EndDate columns to the customer dimension.
B. Add an IsCurrent column to the customer dimension.
C. Enable Snapshot Isolation on the data warehouse.
D. Add CurrentValue and PreviousValue columns to the customer dimension.
Answer: A
Q7. You are developing a SQL Server Integration Services (SSIS) package that imports data from a relational database to a data warehouse.
You are importing data from a relational table named Projects. The table has change data capture enabled on all columns.
You need to process only the most recent values from rows that have been inserted or updated since the previous execution of the package.
Which query should you use as the data source?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/bb522511.aspx
Reference: http://msdn.microsoft.com/en-us/library/bb510627.aspx
Reference: http://msdn.microsoft.com/en-us/library/cc645937.aspx
Q8. You are developing a SQL Server Integration Services (SSIS) package.
To process complex scientific data originating from a SQL Azure database, a custom task component is added to the project.
You need to ensure that the custom component is deployed on a test environment correctly.
What should you do?
A. Add an OnError event handler to the SSIS project.
B. Open a command prompt and run the gacutil command.
C. Configure the SSIS solution to use the Project Deployment Model.
D. Open a command prompt and run the dtexec /dumperror /conn command.
E. Configure the output of a component in the package data flow to use a data tap.
F. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
G. Open a command prompt and run the dtexec /rep /conn command.
H. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
I. Use an msi file to deploy the package on the server.
J. Open a command prompt and run the dtutil /copy command.
K. Create a reusable custom logging component and use it in the SSIS project.
Answer: B
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/ms403356.aspx
Q9. You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads data from a source system that uses the SQL Server change data capture (CDC) feature.
You have added a CDC Source component to the data flow to read changed data from the source system.
You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations.
Which data flow transformation should you use?
A. Audit
B. Merge Join
C. Merge
D. CDC Splitter
Answer: D
Explanation: The CDC splitter splits a single flow of change rows from a CDC source data flow into different data flows for Insert, Update and Delete operations
Ref: http://msdn.microsoft.com/en-us/library/hh758656.aspx
Q10. You are designing a SQL Server Integration Services (SSIS) 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. OnPostExecute
B. OnWarning
C. OnExecStatusChanged
D. OnVariableValueChanged
Answer: A
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms140223.aspx