You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server 2012 database.
The name of the target instance is passed to the package via an Environment variable.
The Environment variable provides the server name to the Project parameter named ServerName.
The OLE DB Project Connection Manager has been parameterized.
You need to configure the Connection Manager property to accept the parameter.
Which property and parameter should you use? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

Explanation

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

IsolationLevel = ReadCommited.
TransactionOption = Required
References:
http://msdn.microsoft.com/en-us/library/ms137690.aspx
http://msdn.microsoft.com/en-us/library/ms137749.aspx
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtscontainer.isolationlevel.aspx
http://msdn.microsoft.com/en-us/library/ms173763.aspx

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?

• A. Create a surrogate key for the time dimension.
• B. Join the two fact tables.
• C. Merge the fact tables.
• D. Create a time dimension that can join to both fact tables at their respective granularity.

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

You are implementing the indexing strategy for a fact table in a data warehouse. The fact table is named Quotes. The table has no indexes and consists of seven columns:
*[ID]
*[QuoteDate]
*[Open]
*[Close]
*[High]
*[Low]
*[Volume]
Each of the following queries must be able to use a columnstore index:
*SELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND '20101231'.
*SELECT AVG([High] - [Low]) AS [AverageRange] FROM Quotes WHERE [QuoteDate] BETWEEN
'20100101' AND '20101231'.
*SELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate] BETWEEN '20100101' AND '20101231'.
You need to ensure that the indexing strategy meets the requirements. The strategy must also minimize the number and size of the indexes.
What should you do?

• A. Create one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and [QuoteDate].
• B. Create three coiumnstore indexes:
  One containing [QuoteDate] and [Close]
  One containing [QuoteDate], [High], and [Low]
  One containing [QuoteDate] and [Volume]
• C. Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].
• D. Create two columnstore indexes:
  One containing [ID], [QuoteDate], [Volume], and [Close]
  One containing [ID], [QuoteDate], [High], and [Low]

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).

You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages.
All packages in the project must connect to the same database. The server name for the database must be set by using a parameter named ServerParam when any package in the project is executed.
You need to develop this project with the least amount of development effort.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A. Set the Sensitive property of the parameter to True.
• B. Set the Required property of the parameter to True.
• C. Edit the project Connection Manager in Solution Explorer. Set the ServerName property to
  @[$Project::ServerParam].
• D. Create a package parameter named ServerName in each package.
• E. Create a project parameter named ServerName.
• F. Edit each package Connection Manager. Set the ServerName property to @[$Project::ServerParam].

Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).