You are designing a SQL Server Integration Services (SSIS) 2012 package that imports data
from a Windows Azure SQL Database database into a SQL Server database.
The SSIS package has the following requirements:
Every night, a very large amount of data is imported into the staging database.
Package processing time must be minimized.
The package must run on its own dedicated server when it is deployed to production.
Transaction log activity when data is imported must be minimized.
You need to design the package to meet the requirements.
Which destination component should you use?

You are implementing a SQL Server Integration Services (SSIS) 2012 package that loads
data from various flat files and a Windows Azure SQL Database database.
Daily transactions must be loaded into a staging database. All the SSIS tasks will use the
CurrentDate variable as the transaction date.
You need to set the CurrentDate variable to the date stored in a control table of the Windows
Azure SQL Database database when the package starts. You need to achieve this goal by
using the least amount of development effort.
What should you use to set the variable?

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?

HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package.
The package control flow will contain many tasks. The tasks will execute consecutively and
none will execute more than once. Certain groups of tasks will share variable and
transaction scope.
You need to group tasks together while enabling them to be collapsed and expanded as a group.
Which item should you use from the SSIS Toolbox? (To answer, select the appropriate item
in the answer area.)

HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package to load data into a
SQL Server 2012 database.
The name of the target instance is passed to the package via an Environment variable.
The Environment variable provides the server name to the Project parameter named
ServerName.
The OLE DB Project Connection Manager has been parameterized.
You need to configure the Connection Manager property to accept the parameter.
Which property and parameter should you use? (To answer, configure the appropriate option
or options in the dialog box in the answer area.)

DRAG DROP
You administer a large and complex SQL Server Integration Services (SSIS) solution in the
SSIS catalog.
You are instructed to execute a package by using PowerShell. You need to create the
correct PowerShell command.
How should you place the four code segments in sequence? (To answer, move the
appropriate code segments from the list of code segments to the answer area and arrange
them in the correct order.)

HOTSPOT
You are the Master Data Services (MDS) administrator at your company.
An existing user needs to integrate data from other systems into MDS.
You need to give the user permissions to integrate data.
Which user management menu item should you select? (To answer, configure the
appropriate option or options in the dialog box in the answer area.)

You are designing a data warehouse that contains a customer dimension.
The customer dimension contains the original customer attributes as well as the most recent
set of updated attributes. When a customer is updated, the original customer attributes must
be preserved.
The customer dimension design must:
Support a Type 3 Slowly Changing Dimension (SCD) process
Minimize the total storage consumed
You need to design a solution that meets these requirements.
What should you do?

You are designing a data warehouse hosted on Windows Azure SQL Database. The data
warehouse currently includes the dimUser and dimRegion dimension tables and the
factSales fact table. The dimUser table contains records for each user permitted to run
reports against the warehouse, and the dimRegion table contains information about sales
regions.
The system is accessed by users from certain regions, 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 regions. Some users must be permitted to see sales data from multiple regions.
What should you do?

You are developing a SQL Server Integration Services (SSIS) package.
The package uses a data flow task to source data from a SQL Server database for loading
into a dimension table in a data warehouse.
You need to create a separate data flow path for data that has been modified since it was
last processed.
Which data flow components should you use to identify modified data? (Each correct answer
presents a complete solution. Choose all that apply.)