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?

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 administering SQL Server Integration Services (SSIS) permissions on a production
server that runs SQL Server 2012.
Quality Assurance (QA) testers in the company must have permission to perform the
following tasks on specific projects:
View and validate projects and packages
View Environments and Environment variables
Execute packages
You need to grant the minimum possible privileges to the QA testers.
What should you do? (Each correct answer presents part of the solution. Choose all that
apply.)

You are designing a data warehouse that uses SQL Server 2012.
The data warehouse contains a table named factSales that stores product sales. The table
has a clustered index on the primary key, four foreign keys to dimension tables, and an
aggregate column for sales totals. All key columns use the int data type and the aggregate
column uses the money data type.
You need to increase the speed of data retrieval from the factSales table.
Which index type should you add to the table?

You are designing a data warehouse that contains a product dimension.
The product dimension contains the original product attributes as well as the most recent set
of updated attributes. When a product is updated, the original product attributes must be
preserved.
The product 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?

DRAG DROP
You are building a fact table in a data warehouse.
The table must have a columnstore index. The table cannot be partitioned.
You need to design the fact table and load it with data.
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 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?

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?

You are performance tuning a SQL Server Integration Services (SSIS) package to load sales
data from a source system into a data warehouse that is hosted on Windows Azure SQL
Database.
The package contains a data flow task that has seven source-to-destination execution trees.
Only three of the source-to-destination execution trees are running in parallel.
You need to ensure that all the execution trees run in parallel.
What should you do?

You develop a SQL Server Integration Services (SSIS) package in a project by using the
Project Deployment Model. It is regularly executed within a multi-step SQL Server Agent job.
You make changes to the package that should improve performance.
You need to establish if there is a trend in the durations of the next 10 successful executions
of the package. You need to use the least amount of administrative effort to achieve this
goal.

What should you do?