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?

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?

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 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?

DRAG DROP
You administer a Microsoft SQL Server database. Service accounts for SQL Agents 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.)

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

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.

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 create a unique constraint?

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 use an identity specification to include a seed of 1,000 and an increment
of 1?