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.)

You administer a Microsoft SQL Server database. Youwant to import data from a text file to the database. You
need to ensure that the following requirements are met:
Data import is performed by using a stored procedure.
Data is loaded as a unit and is minimally logged.
Which data import command and recovery model shouldyou choose? (To answer, drag the appropriate data
import command or recovery model to the appropriatelocation or locations in the answer area. Each data
import command or recovery model may be used once, more than once, or not at all. You may need to dragthe
split bar between panes or scroll to view content.)

DRAG AND DROP
You are designing a package control flow. The package moves sales order data from a SQL Azure
transactional database to an on-premise reporting database. The package will run several times a day, while
new sales orders are being added to the transactional database. The current design of the package control flow
is shown in the answer area. (Refer to the Exhibit.)

The Insert New Orders Data Flow task must meet the following requirements:
Usage of the tempdb database should not be impacted.
Concurrency should be maximized, while only readingcommitted transactions.
If the task fails, only that task needs to be rolled back.
You need to configure the Insert New Orders Data Flow task to meet the requirements. How should you
configure the transaction properties? (To answer, select the appropriate setting or settings in the answer area.)

You are designing an extract, transform, load (ETL)process with SQL Server Integration Services (SSIS). Two
packages, Package A and Package B, will be designed. Package A will execute Package B. Both packages
must reference a file path corresponding to an input folder where files will be located for further processing. You
need to design a solution so that the file path canbe easily configured with the least administrativeand
development effort. Which four actions should you perform in sequence? (To answer, move the appropriate
actions from the list of actions to the answer areaand arrange them in the correct order.)

You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse.

You need to establish the correct order for loadingeach table to maximize parallel processing. Which order
should you use?

You are developing a SQL Server Integration Services (SSIS) package. You need to design a package to
change a variable value during package execution byusing the least amount of development effort. What
should you use?

You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasksand
no other tasks. The three Execute SQL tasks modify products in staging tables in preparation for a data
warehouse load. The package and all three Execute SQL product tasks have their TransactionOption property
set to Supported. You need to ensure that if any ofthe three Execute SQL product tasks fail, all three tasks will
roll back their changes. What should you do?

You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed.
You plan to deploy new SSIS packages to the server.The SSIS packages use the Project Deployment Model
together with parameters and Integration Services environment variables. You need to configure the SQL
Server environment to support these packages. What should you do?

You are developing a SQL Server Integration Services (SSIS) package that imports data into a data
warehouse. You add an Execute SQL task to the control flow. The task must execute a simple INSERT
statement. The task has the following requirements:
The INSERT statement must use the value of a stringpackage variable. The variable name is StringVar.
The Execute SQL task must use an OLE DB Connection Manager.
In the Parameter Mapping tab of the Execute SQL task, StringVar has been added as the only parameter. You
must configure the SQLStatement property of the Execute SQL task. Which SQL statement should you use?

You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on
Server A. 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?