Q1. You are maintaining a SQL Server Integration Services (SSIS) package. The package uses custom functionality that is implemented in Microsoft Visual C. 

The implementation of the custom functionality changes overtime. The design of the package allows you to deploy new releases of the custom functionality without redeploying the entire package. 

You need to implement and deploy an update to the custom functionality without requiring package redeployment. 

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

Q2. You are developing a SQL Server Integration Services (SSIS) package. 

The package sources data from an HTML web page that lists product stock levels. 

You need to implement a data flow task that reads the product stock levels from the HTML web page. 

Which data flow source should you use? 

A. Use an msi file to deploy the package on the server. 

B. Open a command prompt and run the dtutil /copy command. 

C. Add an OnError event handler to the SSIS project. 

D. Open a command prompt and run the gacutil command. 

View Answer

Q3. You are creating a SQL Server Master Data Services (MDS) model for a company. 

The source data for the company is stored in a single table that contains the manager-to-subordinate relationships. 

You need to create a hierarchy representing the organizational structure of the company. 

Which hierarchy type should you use? 

A. Organizational 

B. Recursive 

C. Non-Mandatory Explicit 

D. Many-to-Many 

View Answer

Q4. You are editing a SQL Server Integration Services (SSIS) package that contains a task with a sensitive property. 

You need to create a project parameter and configure it so that its value is encrypted when it is deployed to the SSIS catalog. 

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

Q5. You are installing SQL Server Data Quality Services (DQS). 

You need to give users belonging to a specific Active Directory group access to the Data Quality Server. 

Which SQL Server application should you use? 

A. Data Quality Client with administrative credentials 

B. SQL Server Configuration Manager with local administrative credentials 

C. SQL Server Data Tools with local administrative permissions 

D. SQL Server Management Studio with administrative credentials 

View Answer

Q6. You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS). 

The package includes custom scripts that must be upgraded. 

You need to upgrade the package to SQL Server 2012. 

Which tool should you use? 

A. SQL Server Configuration Manager 

B. SQL Server Agent 

C. SSIS Upgrade Wizard in SQL Server Management Studio 

D. SQL Server DTExecUI utility (dtexecui.exe) 

View Answer

Q7. You are creating a SQL Server Integration Services (SSIS) package to populate a fact table from a source table. The fact table and source table are located in a SQL Azure database. The source table has a price field and a tax field. The OLE DB source uses the data access mode of Table. 

You have the following requirements: 

. The fact table must populate a column named TotalCost that computes the sum of the price and tax columns. . Before the sum is calculated, any records that have a price of zero must be discarded. 

You need to create the SSIS package in SQL Server Data Tools. 

In what sequence should you order four of the listed components for the data flow task? (To answer, move the appropriate components from the list of components to the answer area and arrange them in the correct order.) 

View Answer

Q8. You are developing a SQL Server Integration Services (SSIS) package. The data source 

for the data flow task is a table that has been configured as a change data capture (CDC) table. You are using a CDC Source component to obtain the CDC data. 

The CDC Source component has the following requirements: 

. The output must include metadata columns that indicate which source columns have changed. . The output must return only one change row per source row that is modified in the current CDC processing range. 

You need to configure the CDC Source component. 

Which CDC processing mode should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.) 

View Answer

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

A. Add an OnError event handler to the SSIS project. 

B. Open a command prompt and run the gacutil command. 

C. Configure the SSIS solution to use the Project Deployment Model. 

D. Open a command prompt and run the dtexec /dumperror /conn command. 

E. Configure the output of a component in the package data flow to use a data tap. 

F. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility. 

G. Open a command prompt and run the dtexec /rep /conn command. 

H. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server. 

I. Use an msi file to deploy the package on the server. 

J. Open a command prompt and run the dtutil /copy command. 

K. Create a reusable custom logging component and use it in the SSIS project. 

View Answer

Q10. Topic 2) 

You are the administrator for a Data Quality Server. You are adding a user who must have permission to: 

Edit and execute a project 

View the activity monitoring data 

This user must not be able to: 

Perform any kind of knowledge management 

Create or change a knowledge base 

Terminate an activity or perform administrative duties 

You need to develop a Transact-SQL (T-SQL) script to meet these requirements. 

What should you do? (To answer, drag the appropriate code segment or segments to the correct location or locations in the answer area.) 

View Answer