You are implementing a SQL Server Integration Services (SSIS) package that imports
Microsoft Excel workbook data into a Windows Azure SQL Database database. The
package has been deployed to a production server that runs Windows Server 2008 R2 and
SQL Server 2012.
The package fails when executed on the production server.
You need to ensure that the package can load the Excel workbook data without errors. You
need to use the least amount of administrative effort to achieve this goal.
What should you do?

HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package.
You use a Data Profiling task to examine the data from a source system. You need to
establish:
The minimum and maximum dates for the datetime columns in the source data
The minimum, maximum, and average values for numeric columns in the source data
You need to use the appropriate profile type in the Data Profiling task.
Which profile type should you use? (To answer, select the appropriate profile type in the
answer area.)

DRAG DROP
Your company is evaluating the data cleansing capabilities of SQL Server Data Quality
Services (DQS).
A stand-alone server will be used to host DQS, as well as all related services necessary to
run this service.
You need to install the DQS components on the server by using the minimum amount of
steps possible. You also need to grant permissions to a login named DataStewards to
enable DataStewards to edit and execute DQS projects.
Which four 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.)

HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package. The data source for
the data flow task is a table that has been configured as a change data capture (CDC) table.
You are using a CDC Source component to obtain the CDC data.
The data source will be polled once per hour. The data is updated with multiple important
status changes per minute.
For each captured data change, the before and after values must be included.
You need to configure the CDC Source component.
Which CDC processing mode should you select? (To answer, configure the appropriate
option in the dialog box in the answer area.)

HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package.
OnError and OnWarning events must be logged for viewing in the built-in SSIS reports by
using SQL Server Management Studio.
You need to execute the package and minimize the number of event types that are logged.
Which setting should you use? (To answer, change the appropriate setting in the answer
area.)

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?

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 10 source-to-destination
execution trees.
Only five 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 are the administrator of a server that hosts Data Quality Server for a large retail
company.
The server had a hardware failure during business hours.
You need to restore the server that hosts Data Quality Server to another server. You have a
recent backup of all the required databases.
What should you do? (Each correct answer presents part of the solution. Choose all that
apply.)

To support the implementation of new reports, Active Directory data will be downloaded to a
SQL Server database by using a SQL Server Integration Services (SSIS) 2012 package.
The following requirements must be met:
All the user information for a given Active Directory group must be downloaded to a SQL
Server table. The download process must traverse the Active Directory hierarchy recursively.
You need to configure the package to meet the requirements by using the least development
effort.
What should you use?

You are designing an extract, transform, and load (ETL) solution that loads data into
dimension tables. The ETL process involves many transformation steps.
You need to ensure that the design can provide:
• Auditing information for compliance and business user acceptance
• Tracking and unique identification of records for troubleshooting and error correction
What should you do?