You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2012.
You attempt to add a SQL login, [User1], to the database. However, you receive the following error
message:
“User already exists in current database.”
You need to configure the [User1] login to be able to access the Orders database and retain the
original permissions. You need to achieve this goal by using the minimum required permissions.
Which Transact-SQL statement should you use?

You administer a Microsoft SQL Server 2012 database. You create an availability group named
haContosoDbs. Your primary replica is available at Server01\Contoso01. You need to configure the
availability group to minimize transaction latency on any available secondary databases. In the event
of a database failure, the designated secondary database should come online automatically. Which
Transact-SQL statement should you use?

You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a
SQL Azure database into a data warehouse. The source system contains redundant or inconsistent
data. When the package finds invalid data, the row containing the invalid data must be omitted but
it must also be written to a text file for further analysis. You need to establish the best technique to
log these invalid rows while keeping the amount of development effort to a minimum. What should
you do?

To ease the debugging of packages, you standardize the SQL Server Integration Services (SSIS)
package logging methodology. The methodology has the following requirements:
• Centralized logging in SQL Server
• Simple deployment
• Availability of log information through reports or T-SQL
• Automatic purge of older log entries
• Configurable log details
You need to configure a logging methodology that meets the requirements while minimizing the
amount of deployment and development effort. What should you do?

You are developing a SQL Server Integration Services (SSIS) package. To process complex scientific
data originating from a SQL Azure database, a custom task component is added to the project. You
need to ensure that the custom component is deployed on a test environment correctly. What
should you do?

You are troubleshooting an existing SQL Server Integration Services (SSIS) package. On several
occasions, the package execution does not finish and no data seems to have been transferred. You
need to ensure that package logging occurs. Your solution must minimize deployment and
development efforts. What should you do?

You are developing a SQL Server Integration Services (SSIS) project that copies a large amount of
rows from a SQL Azure database, The project uses the Package Deployment Model. This project is
deployed to SQL Server on a test server. You need to ensure that the project is deployed to the SSIS
catalog on the production server. What should you do?

DRAG DROP
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 can be easily configured
with the least administrative and development effort. 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 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 string package 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?

DRAG DROP
You are building a SQL Server Integration Services (SSIS) package to load data from all files that are
automatically copied to a directory each night through an external FTP process. You need to load
data from all copied files to a destination table in SQL Server. 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.)