You administer a SQL Server Integration Services (SSIS) solution in the SSIS catalog. A SQL Server Agent
job is used to execute a package daily with the basic logging level.
Recently, the package execution failed because of a primary key violation when the package inserted
data into the destination table.
You need to identify all previous times that the package execution failed because of a primary key
violation.
What should you do?

You are developing a SQL Server Integration Services (SSIS) package to load data into a Windows Azure
SQL Database database. The package consists of several data flow tasks.
The package has the following auditing requirements:
 If a data flow task fails, a Transact-SQL (T-SQL) script must be executed.
 The T-SQL script must be executed only once per data flow task that
fails, regardless of the nature of the error.
You need to ensure that auditing is configured to meet these requirements.
What should you do?

You are developing a SQL Server Integration Services (SSIS) project with multiple packages to copy data
to a Windows Azure SQL Database database.
An automated process must validate all related Environment references, parameter data types, package
references, and referenced assemblies. The automated process must run on a regular schedule.You need to establish the automated validation process by using the least amount of administrative
effort.
What should you do?

You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment
Model. All packages in the project must log custom messages.
You need to produce reports that combine the custom log messages with the system-generated log
messages. What should you do?

You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data
load strategy. The package reads data from a source system that uses the SQL Server change data
capture (CDC) feature.
You have added a CDC Source component to the data flow to read changed data from the source
system.
You need to add a data flow transformation to redirect rows for separate processing of insert, update,
and delete operations.
Which data flow transformation should you use?

A SQL Server Integration Services (SSIS) package imports daily transactions from several files into a SQL
Server table named Transaction. Each file corresponds to a different store and is imported in parallel
with the other files. The data flow tasks use OLE DB destinations in fast load data access mode.
The number of daily transactions per store can be very large and is growing. The Transaction table does
not have any indexes.
You need to minimize the package execution time.
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?

Your team is creating SQL Server Integration Services (SSIS) packages that have several dependencies.
The packages use parameters for configuration purposes.
Your company’s IT policies include the following change control requirements:
 After a package is ready for deployment, your team must hand over the process to junior IT
personnel.
 The process must guarantee that when a package has been validated, that same package (and
all its dependencies) is deployed to production.
 The process must be repeatable and reliable and must be executed with the least administrative
and training effort by junior IT personnel.You need to use the most appropriate deployment unit to satisfy the company policies, while minimizing
issues such as incorrect version or configuration.
Which type of deployment unit should you use?

You are developing a SQL Server Integration Services (SSIS) package.
The package sources data from an HTML web page that lists product stock levels.
You need to implement a data flow task that reads the product stock levels from the HTML web page.
Which data flow source should you use?

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?