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?

To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a logging
methodology is put in place.
The methodology has the following requirements:
• The deployment process must be simplified.
• All the logs must be centralized in SQL Server.
• Log data must be available via reports or T-SQL.
• Log archival must be automated.
You need to configure a logging methodology that meets the requirements while minimizing the amount
of deployment and development effort.
What should you do?

You are developing a SQL Server Integration Services (SSIS) project that copies a large amount of rows
from a SQL Azure database. The project uses the Package Deployment Model. This project is deployed to
SQL Server on a test server.
You need to ensure that the project is deployed to the SSIS catalog on the production server.
What should you do?

You are developing a SQL Server Integration Services (SSIS) package.
To process complex scientific data originating from a SQL Azure database, a custom task component is
added to the project.
You need to ensure that the custom component is deployed on a test environment correctly.
What should you do?

You develop and deploy a SQL Server Integration Services (SSIS) package.
The package is stored in the file system.
You need to execute the package without importing it to the SSIS server.
What should you use to execute the package? (Each correct answer presents a complete solution.
Choose all that apply.)

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

You are developing a data flow transformation to merge two data sources. One source contains product
data and the other source contains data about the country in which the product was manufactured.
Both data sources contain a two-character CountryCode column and both use SQL Server. Both data
sources contain an ORDER BY clause to sort the data by the CountryCode column in ascending order.
You use a Merge Join transformation to join the data.

You need to ensure that the Merge Join transformation works correctly without additional
transformations.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

DRAG DROP
You use SQL Server Integration Services (SSIS) for extract, transformation, load (ETL) processing.
Issues concerning addresses are discovered in the data warehouse that you manage. Validation must
separate the addresses into three categories:
 Valid addresses
 Autocorrected addresses
 Invalid addresses
You need to enhance the SSIS packages to perform address validation by using an external service.
Which transformation should you use? (To answer, drag the appropriate transformation from the list of
options to the correct location or locations in the answer area.)

You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two
different sources. One source is hosted in a SQL Azure database. Each source contains products for
different distributors.
Products for each distributor source must be combined for insertion into a single product table
destination.
You need to select the appropriate data flow transformation to meet this requirement.
Which transformation types should you use? (Each correct answer presents a complete solution. Choose
all that apply.)

HOTSPOT
You are developing a data flow to load sales data into a fact table. In the data flow, you configure a
Lookup Transformation in full cache mode to look up the product data for the sale.
The lookup source for the product data is contained in two tables.
You need to set the data source for the lookup to be a query that combines the two tables.
Which page of the Lookup Transformation Editor should you select to configure the query? To answer,
select the appropriate page in the answer area.