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

A. Execute the catalog.add_data_tap stored procedure with the package execution_id. 

B. Execute the catalog.create_execution_dump stored procedure with the package execution_id. 

C. Run the DTEXEC utility with the /DumpOnError option. 

D. Run the DTEXEC utility with the /Reporting V option. 

View Answer

Q2. You are installing SQL Server Data Quality Services (DQS). 

You need to give specific users access to the Data Quality Server. 

Which SQL Server application should you use? 

A. SQL Server Configuration Manager 

B. SQL Server Data Tools 

C. SQL Server Management Studio 

D. Data Quality Client 

View Answer

Q3. 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 view on the sales table. 

B. Partition the fact tables by day. 

C. Create a surrogate key for the time dimension. 

D. Change the level of granularity in both fact tables to be the same. 

View Answer

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

The package must run a parameterized query against a Windows Azure SQL Database 

database. 

You need to use the least amount of development effort to meet the package requirement. 

Which task should you use? (To answer, select the appropriate task in the answer area.) 

View Answer

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

View Answer

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

View Answer

Q7. You are the data steward for a Business Intelligence project. 

You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project. 

You need to produce the CSV file with the least amount of development effort. 

What should you do? 

A. Create an Integration Services package and use a Data Profiling transform. 

B. Create a custom .NET application based on the Knowledgebase class. 

C. Create a data quality project. 

D. Create a CLR stored procedure based on the Knowledgebase class. 

E. Create a Master Data Services (MDS) business rule. 

View Answer

Q8. You develop a SQL Server Integration Services (SSIS) project by using the Project Deployment model. 

The project contains many packages. It is deployed on a server named Development!. The project will be deployed to several servers that run SQL Server 2012. 

The project accepts one required parameter. The data type of the parameter is a string. 

A SQL Agent job is created that will call the master.dtsx package in the project. A job step is created for the SSIS package. 

The job must pass the value of an SSIS Environment Variable to the project parameter. The value of the Environment Variable must be configured differently on each server that runs SQL Server. The value of the Environment Variable must provide the server name to the project parameter. 

You need to configure SSIS on the Development1 server to pass the Environment Variable to the package. 

Which four actions should you perform in sequence by using SQL Server Management Studio? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.) 

View Answer

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. Depending on the value in a source column, the package redirects rows to one of five different data flow paths. 

You need to add a data flow transformation to support the package redirection. 

Which data flow transformation should you use? 

A. Conditional Split 

B. pjvot 

C. Multicast 

D. Lookup 

View Answer

Q10. 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.set_environment_reference_type 

B. catalog.set_environment_property 

C. catalog.create_environment_reference 

D. catalog.create_environment 

View Answer