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

Select “IsolationLeve as Choas” 

View Answer

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

View Answer

Q3. You are developing a SQL Server Integration Services (SSIS) package. 

The package sources data from an HTML web page that lists product stock levels. 

You need to implement a data flow task that reads the product stock levels from the HTML web page. 

Which data flow source should you use? 

A. Raw File source 

B. XML source 

C. Custom source component 

D. Flat File source 

View Answer

Q4. You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model. 

The project will be deployed to an SSIS catalog folder where Environments have already been created. You need to deploy the project. 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. 

View Answer

Q5. You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages. 

All packages in the project must connect to the same database. The server name for the database must be set by using a parameter named ParamConnection when any package in the project is executed. 

You need to develop this project with the least amount of development effort. 

What should you do? (Each answer presents a part of the solution. Choose all that apply.) 

A. Create a package parameter named ConnectionName in each package. 

B. Edit each package Connection Manager. Set the ConnectionName property to @[$Project::ParamConnection]. 

C. Edit the project Connection Manager in Solution Explorer. Set the ConnectionName property to @ [$Project::ParamConnection]. 

D. Set the Sensitive property of the parameter to True. 

E. Create a project parameter named ConnectionName. 

F. Set the Required property of the parameter to True. 

View Answer

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

A. In the SSISDB database, add QA Tester logons to the ssis_admin role. 

B. In the msdb database, add QA Tester logons to the db_ssisoperator role. 

C. Grant Modify permission in the projects to the QA Tester logons. 

D. Grant Read permission in the SSIS catalog folder, the projects, and the Environments to the QA Tester logons. 

E. Grant Execute permission in the projects to the QA Tester logons. 

F. In the msdb database, add QA Tester logons to the db_ssisItduser role. 

View Answer

Q7. A new SQL Server Integration Services (SSIS) project is deployed to the SSIS catalog. 

To troubleshoot some data issues, you must output the data streaming through several data flows into text files for further analysis. You have the list of data flow package paths and identification strings of the various task components that must be analyzed. 

You need to create these output files with the least amount of administrative and development effort. 

Which three stored procedures should you execute in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.) 

View Answer

Q8. 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 junk dimension for the geography dimension. 

B. Implement change data capture. 

C. Create a conformed dimension for the geography dimension. 

D. Create three geography dimensions. 

View Answer

Q9. You are developing a SQL Server Integration Services (SSIS) package. 

The package uses custom functionality that accesses a SQL Server database. The custom functionality must be implemented by using Language Integrated Query (LINQ). 

You need to ensure that the LINQ code can be debugged at design time. 

What should you select from the SSIS Toolbox? (To answer, drag the appropriate item to 

the correct location in the answer area.) 

View Answer

Q10. A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse. 

The SSIS catalog includes two Environments: 

. Development 

. QA 

Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the development or QA data warehouses. 

You need to be able to execute deployed packages by using either of the defined Environments. 

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

View Answer