Q1. You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named ProductCode. 

Some of the data to be loaded will reference products that need special processing logic in the data flow. 

You need to enable separate processing streams for a subset of rows based on the source product code. 

Which data flow transformation should you use? 

A. Source Assistant 

B. Destination Assistant 

C. Audit 

D. Script Component 

View Answer

Q2. A SQL Server Integration Services (SSIS) package named DataFeed interacts with an external vendor data feed. The package is executed several times a day, either as part of other packages' control flow or by itself. The external data feed is unreliable because network failures and slow response times are frequent. The package is currently deployed on the file system. 

To analyze the reliability of the external data feed, you must collect execution data. 

Every time the DataFeed package is executed, the following information must be logged: 

Start Time 

End Time 

Execution Result 

Execution Duration 

You need to design a logging solution that meets the requirements by using the least amount of administrative and development effort. 

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

Q3. You are reviewing the design of a customer dimension table in an existing data warehouse hosted on SQL Azure. 

The current dimension design does not allow the retention of historical changes to customer attributes such as Postcode. 

You need to redesign the dimension to enable the full historical reporting of changes to multiple customer attributes including Postcode. 

What should you do? 

A. Add StartDate and EndDate columns to the customer dimension. 

B. Add an IsCurrent column to the customer dimension. 

C. Enable Snapshot Isolation on the data warehouse. 

D. Add CurrentValue and PreviousValue columns to the customer dimension. 

View Answer

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

Q5. You are the data steward at your company. 

Duplicate customers exist in a Microsoft Excel workbook. You create a Data Quality Services (DQS) knowledge base and matching policy to identify these duplicate customers. 

You need to identify the duplicate customers. 

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

View Answer

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

Q7. You are deploying a new SQL Server Integration Services (SSIS) package to five servers. 

The package must meet the following requirements: 

. .NET Common Language Runtime (CLR) integration in SQL Server must not be enabled. . The Connection Managers used in the package must be configurable without 

editing and redeploying the package. 

. The deployment procedure must be automated as much as possible. 

. Performance must be maximized. 

You need to set up a deployment strategy that meets the requirements. 

What should you do? 

A. Add an OnError event handler to the SSIS project. 

B. Use an msi file to deploy the package on the server. 

C. Open a command prompt and run the gacutil command. 

D. Open a command prompt and run the dtutil /copy command. 

E. Open a command prompt and run the dtexec /rep /conn command. 

F. Open a command prompt and run the dtexec /dumperror /conn command. 

G. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility. 

H. Create a reusable custom logging component and use it in the SSIS project. 

I. Configure the SSIS solution to use the Project Deployment Model. 

J. Configure the output of a component in the package data flow to use a data tap. 

K. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server. 

View Answer

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

The project is deployed to a single SSIS catalog, and transfers data to and from multiple databases hosted on SQL Server. 

The project must be configured to be able to export data to and from five different production servers that run SQL Server 2012. Each target server requires different values for connection strings and parameters in the SSIS project. 

You need to meet the requirements by using the least amount of administrative effort. 

What should you do? 

A. For each target server, create separate registry entry configurations. Select the registry entry at package execution time. 

B. For each target server, create separate Environments in the SSIS catalog of the host SQL Server SSIS instance. Select the appropriate Environment at package execution time. 

C. Create one SSIS catalog Environment. Change the values of each Environment variable at package execution time. 

D. For each target server, create a separate XML configuration file. Select the XML configuration file at package execution time. 

View Answer

Q9. You are installing the Data Quality Server component of Data Quality Services. 

You need to provision the hardware and install the software for the server that runs the 

Data Quality Server. 

You must ensure that the minimum Data Quality Server prerequisites are met. 

What should you do? 

A. Install SQL Server 2012 Database Engine. 

B. Install Microsoft SharePoint Server 2010 Enterprise Edition with PowerPivot. 

C. Make sure the server has at least 4 GB of RAM. 

D. Install Microsoft Internet Explorer 6.0 SP1 or later. 

View Answer

Q10. 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 (?) 

View Answer