You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasksand
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 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 tablehas
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 designing a SQL Server Integration Services(SSIS) data flow to load sales transactions from asource
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?

You are designing a SQL Server Integration Services(SSIS) package that uses the Fuzzy Lookup
transformation. The reference data to be used in the transformation does not change. You need to reusethe
Fuzzy Lookup match index to increase performance and reduce maintenance. What should you do?

You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different
sources. One source is hosted in a SQL Azure database. Each source contains products for different
distributors. Products for each distributor source must be combined for insertion into a single product table
destination. You need to select the appropriate data flow transformation to meet this requirement. Which
transformation types should you use? (Each correct answer presents a complete solution. Choose all that
apply.)

You are implementing the indexing strategy for a fact table in a data warehouse. The fact table is named
Quotes. The table has no indexes and consists of seven columns:
[ID]
[QuoteDate]
[Open]
[Close]
[High]
[Low]
[Volume]
Each of the following queries must be able to use acolumnstore index:
SELECT AVG ([Close]) AS [AverageClose]
FROM Quotes
WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’.
SELECT AVG([High] – [Low]) AS [AverageRange]
FROM Quotes
WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’.
SELECT SUM([Volume]) AS [SumVolume]
FROM Quotes
WHERE [QuoteDate] BETWEEN ‘20100101’ AND ‘20101231’
You need to ensure that the indexing strategy meetsthe requirements. The strategy must also minimize the
number and size of the indexes. What should you do?

You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse
hosted on SQL Azure. The package uses a Foreach container to process text files found in a folder. The
package must be deployed to a single server by using the Project Deployment model. Multiple SQL Server
Agent jobs call the package. Each job is executed on a different schedule. Each job passes a differentfolder
path to the package. You need to configure the package to accept the folder path from each job. Which
package configuration should you use?

You are creating a SQL Server Master Data Services (MDS) model. This model is used to store a master list of
products. An attribute must be added to the Productentity to define the sales manager responsible foreach
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?