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?

You are designing a data warehouse hosted on SQL Azure. The data warehouse currently includes
the dimllser and dimDistrict dimension tables and the factSales fact table. The dimUser table
contains records for each user permitted to run reports against the warehouse, and the dimDistrict
table contains information about sales districts. The system is accessed by users from certain
districts, as well as by area supervisors and users from the corporate headquarters. You need to
design a table structure to ensure that certain users can see sales data for only certain districts.
Some users must be permitted to see sales data from multiple districts. What should you do?

You develop three Microsoft SQL Server 2012 databases named Database1, Database2, and
Database3. You have permissions on both Database1 and Database2. You plan to write and deploy a
stored procedure named dbo.usp_InsertEvent in Database3. dbo.usp_InsertEvent must execute
other stored procedures in the other databases. You need to ensure that callers that do not have
permissions on Database1 or Database2 can execute the stored procedure. Which Transact-SQL
statement should you use?

DRAG DROP
You are creating a sales data warehouse. When a product exists in the product dimension, you
update the product name. When a product does not exist, you insert a new record. In the current
implementation, the DimProduct table must be scanned twice, once for the insert and again for the
update. As a result, inserts and updates to the DimProduct table take longer than expected. You
need to create a solution that uses a single command to perform an update and an insert. How
should you use a MERGE T-SQL statement to accomplish this goal? (To answer, drag the appropriate
answer choice from the list of options to the correct location or locations in the answer area. You
may need to drag the split bar between panes or scroll to view content.)