You are developing a SQL Server Integration Services (SSIS) package that imports data into
a data warehouse hosted on SQL Azure.
The package uses a Foreach container to process text files found in a folder. The package
must be deployed to a single server by using the Project Deployment model.
Multiple SQL Server Agent jobs call the package. Each job is executed on a different
schedule.
Each job passes a different folder path to the package.
You need to configure the package to accept the folder path from each job.
Which package configuration should you use?

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.

DRAG DROP
You are editing a SQL Server Integration Services (SSIS) package that uses checkpoints.
The package performs the following steps:
1. Download a sales transaction file by using FTP.
2. Truncate a staging table.
3. Load the contents of the file to the staging table.
4. Merge the data with another data source for loading to a data warehouse.

The checkpoints are currently working such that if any of the four steps fail, the package will
restart from the failed step the next time it executes.

You need to modify the package to ensure that if either the Truncate Staging Table or the
Load Sales to Staging task fails, the package will always restart from the Truncate Staging
Table task the next time the package runs.
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 designing a SQL Server Integration Services (SS1S) package that uploads a file to
a table named Orders in a SQL Azure database.
The company’s auditing policies have the following requirements:
An entry must be written to a dedicated SQL Server log table named OrderLog.
The entry must be written as soon as the file upload task completes.
You need to meet the company’s policy requirements. Which event handler should you use?

You are designing a data warehouse for a fresh food distribution business that stores sates
by individual product. It stores sales targets by product category. Products are classified into
subcategories and categories.
Each product is included in only a single product subcategory, and each subcategory is
included in only a single category.
The data warehouse will be a data source for an Analysis Services cube.
The data warehouse contains two fact tables:
factSales, used to record daily sales by product
factProductTarget, used to record the monthly sales targets by product category
Reports must be developed against the warehouse that reports product sales by product,
category and subcategory, and product sales targets.
You need to design the product dimension. The solution should use as few tables as
possible while supporting all the requirements.
What should you do?

DRAG DROP
You are creating a sales data warehouse. When a product exists in the product dimension,
you update the product name. When a product does not exist, you insert a new record.
In the current implementation, the DimProduct table must be scanned twice, once for the
insert and again for the update. As a result, inserts and updates to the DimProduct table take
longer than expected.
You need to create a solution that uses a single command to perform an update and an
insert.
How should you use a MERGE T-SQL statement to accomplish this goal? (To answer, drag
the appropriate answer choice from the list of options to the correct location or locations in
the answer area.

You may need to drag the split bar between panes or scroll to view content.)

DRAG DROP
You are building a fact table in a data warehouse.
The table must have a columnstore index. The table cannot be partitioned.
You need to design the fact table and load it with data.
Which three 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.)