70-463 Free Practice Test

You are administering SQL Server Integration Services (SSIS) permissions on a production server that runs SQL Server 2012.
Quality Assurance (QA) testers in the company must have permission to perform the following tasks on specific projects:
View and validate projects and packages
View Environments and Environment variables Execute packages
You need to grant the minimum possible privileges to the QA testers.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

1. A. In the SSISDB database, add QA Tester logons to the ssis_admin role.
2. B. In the msdb database, add QA Tester logons to the db_ssisoperator role.
3. C. Grant Modify permission in the projects to the QA Tester logons.
4. D. Grant Read permission in the SSIS catalog folder, the projects, and the Environments to the QA Tester logons.
5. E. Grant Execute permission in the projects to the QA Tester logons.
6. F. In the msdb database, add QA Tester logons to the db_ssisItduser rol

Correct Answer: BD

You develop a SQL Server Integration Services (SSIS) project by using the Package Deployment Model. A package in the project extracts data from a Windows Azure SQL Database database. The package is deployed to SQL Server.
The package is not producing the desired results.
You need to generate the .mdmp and .tmp debug files in order to troubleshoot the issues. What should you do?

1. A. Execute the catalog.add_data_tap stored procedure with the package execution_id.
2. B. Execute the catalog.create_execution_dump stored procedure with the package execution_id.
3. C. Run the DTEXEC utility with the /DumpOnError option.
4. D. Run the DTEXEC utility with the /Reporting V optio

Correct Answer: C

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.)

Solution:


Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD).
You need to add a task or component to the package that allows you to implement the SCD logic. What should you use?

1. A. a Data Conversion component
2. B. an Execute SQL task that executes a MERGE statement on the database
3. C. a Merge component
4. D. an Expression task

Correct Answer: C
Note: Type of Slowly Changing Dimensions
Slowly Changing Dimensions are categorized into three types named: Type 1, Type 2, and Type3. The Type 1 SCD does not maintain the history of changing attributes, it overwrites values of the attributes. Type 2 maintains historical values for changing attributes. Type 3 that we do not use much maintains separate columns for changed attributes. SSIS SCD wizard supports both Type 1 and Type 2.
Using MERGE instead of SCD wizard
Replacement of SCD wizard with MERGE is not a straightforward technique. If the SCD has both Type 1 and Type 2 types attributes, they need to be handled separately.

You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS).
The package includes custom scripts that must be upgraded. You need to upgrade the package to SQL Server 2012. Which tool should you use?

1. A. SQL Server dtexec utility (dtexec.exe)
2. B. SQL Server DTExecUI utility (dtexecui.exe)
3. C. SSIS Upgrade Wizard in SQL Server Data Tools
4. D. SQL Server Integration Services Deployment Wizard

Correct Answer: C
Use the SSIS Package Upgrade Wizard to upgrade SQL Server 2005 Integration Services (SSIS) packages and SQL Server 2008 Integration Services (SSIS) packages to the package format for the current (2012) release of SQL Server Integration Services.