You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL
tasks and no other tasks. The package and all three Execute SQL tasks have their TransactionOption
property set to Supported. You need to ensure that if any of the Execute SQL tasks fail, all three
tasks will roll back their changes. What should you do?

You are designing a SQL Server Integration Services (SSIS) package that uploads a file to a table
named Orders in a SQL Azure database. The company’s auditing policies have the following
requirements:
• An entry must be written to a dedicated SQL Server log table named OrderLog.
• The entry must be written as soon as the file upload task completes.
You need to meet the company’s policy requirements. Which event handler should you use?

You are developing a SQL Server Integration Services (SSIS) package that imports data from a
relational database to a data warehouse. You are importing data from a relational table named
Projects. The table has change data capture enabled on all columns. You need to process only the
most recent values from rows that have been inserted or updated since the previous execution of
the package. Which query should you use as the data source?

You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server
table on ServerA. The package includes a data flow and is executed on ServerB.
The destination table has its own identity column. The destination data load has the following
requirements:
• The identity values from the source table must be used.
• Default constraints on the destination table must be ignored.
• Batch size must be 100,000 rows.
You need to add a destination and configure it to meet the requirements. Which destination should
you use?

You are adding a new capability to several dozen SQL Server Integration Services (SSIS) packages.
The new capability is not available as an SSIS task. Each package must be extended with the same
new capability. You need to add the new capability to all the packages without copying the code
between packages. 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 SQL Azure. 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?

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.

DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that imports unsorted data into
a data warehouse hosted on SQL Azure. You have the following requirements:
• A destination table must contain all of the data in two source tables.
• Duplicate records must be inserted into the destination table.
You need to develop a data flow that imports the data while meeting the requirements. How should
you develop the data flow? (To answer, drag the appropriate transformation from the list of
transformations to the correct location in the answer area.)

You are using the Knowledge Discovery feature of the Data Quality Services (DQS) client application
to modify an existing knowledge base. In the mapping configuration, two of the three columns are
mapped to existing domains in the knowledge base. The third column, named Team Type, does not
yet have a domain. You need to complete the mapping of the Team Type column. What should you
do?

You are creating a SQL Server Master Data Services (MDS) mode). This model is used to store a
master list of products. An attribute must be added to the Product entity to define the sales
manager responsible for each product. You need to create an attribute in the Product entity that
prevents users from entering invalid sales manager values. Which type of attribute should you
create?