Q1. You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on SQL Azure. One of the columns in the data source is named ProductCode. 

Some of the data to be loaded will reference products that need special processing logic in the data flow. 

You need to enable separate processing streams for a subset of rows based on the source product code. 

Which data flow transformation should you use? 

A. Audit 

B. Source Assistant 

C. Script Task 

D. Conditional Split 

View Answer

Q2. 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 re-entering 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.) 

View Answer

Q3. You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different sources. One source is hosted in a SQL Azure database. Each source contains products for different distributors. 

Products for each distributor source must be combined for insertion into a single product table destination. 

You need to select the appropriate data flow transformation to meet this requirement. 

Which transformation types should you use? (Each answer represents a complete solution. Choose all that apply.) 

A. Slowly Changing Dimension 

B. pivot 

C. Lookup 

D. Union All 

E. Merge 

View Answer

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

View Answer

Q5. You are using the Master Data Services (MDS) Add-in for Excel to configure the entities in a model- The model consists of two entities: one named Customer and one named State. You opened the Customer entity. 

Currently, data stewards can enter any text value in the Customer entity's State attribute. You must restrict the entry of values in the State attribute to members defined in the State entity. 

You need to configure the State attribute of the Customer entity. 

Which command should you use? (To answer, select the appropriate command in the answer area.) 

View Answer

Q6. You administer a Microsoft SQL Server database. You want to import data from a text file to the database. 

You need to ensure that the following requirements are met: 

. Data import is performed by using a stored procedure. 

. Data is loaded as a unit and is minimally logged. 

Which data import command and recovery model should you choose? (To answer, drag the appropriate data import command or recovery model to the appropriate location or locations in the answer area. Each data import command or recovery model may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.) 

View Answer

Q7. You are designing a data warehouse hosted on Windows Azure SQL Database. The data warehouse currently includes the dimUser and dimRegion dimension tables and the factSales fact table. The dimUser table contains records for each user permitted to run reports against the warehouse, and the dimRegion table contains information about sales regions. 

The system is accessed by users from certain regions, as well as by area supervisors and users from the corporate headquarters. 

You need to design a table structure to ensure that certain users can see sales data for only certain regions. Some users must be permitted to see sales data from multiple regions. 

What should you do? 

A. For each region, create a view of the factSales table that includes a WHERE clause for the region. 

B. Create a userRegion table that contains primary key columns from the dimUser and dimRegion tables. 

C. Add a region column to the dimUser table. 

D. Partition the factSales table on the region column. 

View Answer

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

View Answer

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

A. In the SSISDB.[catalog], query the .[executions] view. 

B. In the SSISDB.[catalog] , query the [event_messages] view. 

C. In SQL Server Management Studio, view the SQL Agent Job History. 

D. In SQL Server Management Studio, view the SQL Agent Error Log. 

E. In SQL Server Management Studio, view the SQL Server Log. 

View Answer

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

Select “IsolationLeve as Choas” 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 

View Answer