Q1. A Data Flow task in a SQL Server Integration Services (SSIS) package produces run-time errors. 

You need to edit the package to log specific error messages. 

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

Q2. You are designing a data warehouse for a fresh food distribution business that stores sates by individual product. It stores sales targets by product category. Products are classified into subcategories and categories. 

Each product is included in only a single product subcategory, and each subcategory is included in only a single category. 

The data warehouse will be a data source for an Analysis Services cube. 

The data warehouse contains two fact tables: 

. factSales, used to record daily sales by product 

. factProductTarget, used to record the monthly sales targets by product category 

Reports must be developed against the warehouse that reports product sales by product, category and subcategory, and product sales targets. 

You need to design the product dimension. The solution should use as few tables as possible while supporting all the requirements. 

What should you do? 

A. Create two product tables, dimProduct and dimProductCategory. Connect factSales to dimProduct and factProductTarget to dimProductCategory with foreign key constraints. Direct the cube developer to use key granularity attributes. 

B. Create one product table, dimProduct, which contains product detail, category, and subcategory columns. Connect factSales to dimProduct with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for factProductTarget. 

C. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory, and a fourth bridge table that joins products to their appropriate category and subcategory table records with foreign key constraints. Direct the cube developer to use key granularity attributes. 

D. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory. Connect factSales to all three product tables and connect factProductTarget to dimProductCategory with foreign key constraints. Direct the cube developer to use key granularity attributes. 

View Answer

Q3. You are editing a SQL Server Integration Services (SSIS) project named Project1 in SQL Server Data Tools. 

A package Connection Manager has been parameterized with project scope. 

You need to display the parameters that have been generated for the Connection Manager. 

What should you use? To answer, select the appropriate setting or settings in the answer area. 

View Answer

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

task in the package checks product stock levels and sets a package variable named InStock to TRUE or FALSE depending on the stock level found. 

After the successful execution of the Execute SQL task, one of two data flow tasks must run, depending on the value of the InStock variable. 

You need to set the precedence constraints. 

Which value for the evaluation operation should you use? (To answer, select the appropriate option for the evaluation operation in the answer area.) 

View Answer

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

View Answer

Q6. Your company uses a proprietary encryption algorithm to secure sensitive data. A custom cryptographic assembly was developed in Microsoft .NET and is used in several applications. 

A SQL Server Integration Services (SSIS) package is importing data into a Windows Azure SQL Database database. Some of the data must be encrypted with the proprietary encryption algorithm. 

You need to design the implementation strategy to meet the requirements while minimizing development and deployment effort and maximizing data flow performance. 

What should you do? 

A. Create a SQL Common Language Runtime (SQLCLR) function that uses the custom assembly to encrypt the data, deploy it in the Windows Azure SQL Database database, and use it when inserting data. 

B. Use an SSIS Script transformation that uses the custom assembly to encrypt the data when inserting it. 

C. Create a SQL Common Language Runtime (SQLCLR) stored procedure that uses the custom assembly to encrypt the data, deploy it in the Windows Azure SQL Database database, and use it when inserting data. 

D. Use an SSIS Script task that uses the custom assembly to encrypt the data when inserting it. 

View Answer

Q7. You are designing a data warehouse that uses SQL Server 2012. You are preparing to update the contents of a fact table that contains a non-clustered columnstore index. 

You need to run an update statement against the table. 

What should you do first? 

A. Pause the columnstore index. 

B. Change the recovery model of the database to Bulk-logged. 

C. Change the non-clustered columnstore index to be a clustered columnstore index. 

D. Drop the columnstore index. 

View Answer

Q8. You are designing a complex SQL Server Integration Services (SSIS) project that uses the Project Deployment model. 

The project will contain between 15 and 20 packages. All the packages must connect to the same data source and destination. 

You need to define and reuse the connection managers in all the packages by using the least development effort. 

What should you do? 

A. Copy and paste the connection manager details into each package. 

B. Implement project connection managers. 

C. Implement package connection managers. 

D. Implement parent package variables in all packages. 

View Answer

Q9. You are preparing to install SQL Server 2012 Master Data Services (MDS). 

You need to ensure that the database requirements are met. 

What should you install? 

A. Microsoft SharePoint Server 2010 Standard Edition SP1 

B. Microsoft SharePoint Server 2010 Enterprise Edition SP1 

C. SQL Server 2012 Data Center (64-bit) x64 on the database server 

D. SQL Server 2012 Enterprise (64-bit) x64 on the database server 

View Answer

Q10. You are loading a dataset into SQL Server. The dataset contains numerous duplicates for the Artist and Song columns. 

The values in the Artist column in the dataset must exactly match the values in the Artist domain in the knowledge base. The values in the Song column in the dataset can be a close match with the values in the Song domain. 

You need to use SQL Server Data Quality Services (DQS) to define a matching policy rule to identify duplicates. 

How should you configure the Rule Editor? (To answer, drag the appropriate answers to the answer area.) 

View Answer