DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that is ready for
deployment to a production server. The package contains sensitive information secured by
using the EncryptSensitiveWithUserKey package protection level.
You are preparing the package for deployment by the production operations team.
You need to ensure that the production operations team can open and execute the package
without re-entering the sensitive information.
Which three steps should you perform in sequence? (To answer, move the appropriate
actions from the list of actions to the answer area and arrange them in the correct order.)

You are developing a project that contains multiple SQL Server Integration Services (SSIS)
packages. The packages will be deployed to the SSIS catalog. One of the steps in each
package accesses an FTP site to download data files.
You create project parameters to store the username and password that are used to access
the FTP site.
You need to ensure that the username and password values are encrypted when they are
deployed.
What should you do?

A SQL Server Integration Services (SSIS) package was deployed two weeks ago with the
Project Deployment Model.
Sometimes the package is started as part of a multistep SQL job. At other times, the
package is started manually by a database administrator by using the Object Explorer in
SQL Server Management Studio.
You need to identify the authenticated user responsible for starting the package each time it
executes.
How can you find this information?

DRAG DROP
You administer a Microsoft SQL Server database. You want 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 should you choose? (To answer, drag the
appropriate data import command or recovery model to the appropriate location 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 drag the split bar between panes or scroll to view content.)

CORRECT TEXT
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. (Click the Exhibit button.)

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 reading committed 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 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 designing a data warehouse with two fact tables. The first table contains sales per
month and the second table contains orders per day.
Referential integrity must be enforced declaratively.
You need to design a solution that can join a single time dimension to both fact tables.
What should you do?

HOTSPOT
You administer a Microsoft SQL Server 2012 database. The database contains a table that
has the following definition:

You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard.
You need to ensure that the following requirements are met:
The first row of the file contains the first row of data.
Each record is of the same length.
The date follows the U.S. date format.
The file supports international characters.
What should you do? (To answer, simply select the option or options in the answer area that
you would configure.)

DRAG DROP
You administer a Microsoft SQL Server database. Service accounts for SQL Agent are
configured to use a local user. A Microsoft SQL Server Integration Services (SSIS) job step
has been created within a SQL Server Agent job. The SSIS package accesses a network
share when exporting data from a SQL Server database.
When you execute the SQL Server Agent job, it fails due to a permissions failure on a share
on a remote server. You need to ensure that the SQL Server Agent job can execute the
SSIS package. Which four actions should you perform in sequence? (To answer, move the
appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

You administer a Microsoft SQL Server 2012 database. The database contains a table
named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)

Confidential information about the employees is stored in a separate table named
EmployeeData. One record exists within EmployeeData for each record in the Employee
table. You need to assign the appropriate constraints and table properties to ensure data

integrity and visibility. On which column in the Employee table should you a create a unique
constraint?