DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that imports unsorted data into a
data warehouse hosted on SQL Azure.
You have the following requirements:
• A destination table must contain all of the data in two source tables.
• Duplicate records must be inserted into the destination table.
You need to develop a data flow that imports the data while meeting the requirements.
How should you develop the data flow? (To answer, drag the appropriate transformation from the list of
transformations to the correct location in the answer area.)

HOTSPOT
You are editing a SQL Server Integration Services (SSIS) project named Project1 in SQL Server Data Tools.
A package Connection Manager has been parameterized with project scope.
You need to display the parameters that have been generated for the Connection Manager.
What should you use? To answer, select the appropriate setting or settings in the answer area.

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

You install a SQL Server 2012 database engine instance on a production server. A month later, you install
SQL Server 2012 Integration Services (SSIS).
You must develop an SSIS project and deploy it to the server by using the Project Deployment model.
Operations log records that are outside the configured retention period must be cleaned automatically.
You need to create the SSIS catalog on the production server and ensure that the operations log
cleaning requirement is met.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

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?

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

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?

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