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 develop a SQL Server Integration Services (SSIS) package that imports SQL Azure
data into a data warehouse every night.
The SQL Azure data contains many misspellings and variations of abbreviations. To import
the data, a developer used the Fuzzy Lookup transformation to choose the closest-matching
string from a reference table of allowed values. The number of rows in the reference table is
very large.
If no acceptable match is found, the Fuzzy Lookup transformation passes a null value.
The current setting for the Fuzzy Lookup similarity threshold is 0.50.
Many values are incorrectly matched.
You need to ensure that more accurate matches are made by the Fuzzy Lookup
transformation without degrading performance.
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?

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

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

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?

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?

You are a database developer of a Microsoft SQL Server 2012 database. You are designing
a table that will store Customer data from different sources. The table will include a column
that contains the CustomerID from the source system and a column that contains the
SourceID. A sample of this data is as shown in the following table. You need to ensure that

the table has no duplicate CustomerID within a SourceID. You also need to ensure that the
data in the table is in the order of SourceID and then CustomerID.
Which Transact- SQL statement should you use?

HOTSPOT
You are designing a SQL Server Integration Services (SSIS) package configuration strategy.
The package configuration must meet the following requirements:
Include multiple properties in a configuration.
Support several packages with different configuration settings.
You need to select the appropriate configuration. Which configuration type should you use?
To answer, select the appropriate option from the drop-down list in the dialog box.