Q1. 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.
Q2. 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
Q3. CORRECT TEXT
You are designing a package control flow. The package moves sales order data from a SQL Azure transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being added to the transactional database. The current design of the package control flow is shown in the answer area. (Click the Exhibit button.)
The Insert New Orders Data Flow task must meet the following requirements:
•Usage of the tempdb database should not be impacted.
•Concurrency should be maximized, while only reading committed transactions.
•If the task fails, only that task needs to be rolled back.
You need to configure the Insert New Orders Data Flow task to meet the requirements. How should you configure the transaction properties? (To answer, select the appropriate
setting or settings in the answer area.)
Answer:
IsolationLevel = ReadCommited. TransactionOption = Required References:
http://msdn.microsoft.com/en-us/library/ms137690.aspx http://msdn.microsoft.com/en-us/library/ms137749.aspx http://msdn.microsoft.com/en- us/library/microsoft.sqlserver.dts.runtime.dtscontainer.isolationlevel.aspx http://msdn.microsoft.com/en-us/library/ms173763.aspx
Q4. 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
Q5. You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. You add an Execute SQL task to the control flow.
The task must execute a simple INSERT statement. The task has the following requirements:
•The INSERT statement must use the value of a string package variable. The variable name is StringVar.
•The Execute SQL task must use an OLE DB Connection Manager.
In the Parameter Mapping tab of the Execute SQL task, StringVar has been added as the only parameter.
You must configure the SQLStatement property of the Execute SQL task. Which SQL statement should you use?
A. INSERT INTO dbo.Table (variablevalue) VALUES ($StringVar)
B. INSERT INTO dbo.Table (variablevalue) VALUES (0)
C. INSERT INTO dbo.Table (variablevalue) VALUES (@0)
D. INSERT INTO dbo.Table (variablevalue) VALUES (?)
Answer: D
Explanation: References: http://msdn.microsoft.com/en-us/library/ms141003.aspx http://msdn.microsoft.com/en-us/library/ms140355.aspx http://msdn.microsoft.com/en-us/library/cc280502.aspx
Q6. 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
Q7. CORRECT TEXT
You are designing a package control flow. The package moves sales order data from a SQL Azure transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being added to the transactional database.
The current design of the package control flow is shown in the answer area. (Click the Exhibit button.)
The Insert New Orders Data Flow task must meet the following requirements:
✑ Usage of the tempdb database should not be impacted.
✑ Concurrency should be maximized, while only reading committed transactions.
✑ If the task fails, only that task needs to be rolled back.
You need to configure the Insert New Orders Data Flow task to meet the requirements.
How should you configure the transaction properties? To answer, select the appropriate setting or settings in the answer area.
Answer:
IsolationLevel = ReadCommited. TransactionOption = Required
Q8. 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:
Q9. You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure.
Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart.
You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented.
What should you do?
A. Create a conformed dimension for the geography dimension.
B. Implement change tracking.
C. Create a degenerate dimension for the geography dimension.
D. Create a Type 2 slowly changing dimension for the geography dimension.
Answer: A
Q10. 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. Install the Analysis Services OLE DB Provider.
B. Make the data available for DQS operations.
C. Run the Data Quality Server Installer.
D. Install ADOMD.NET.
Answer: C
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ff877917.aspx http://msdn.microsoft.com/en-us/library/gg492277.aspx