Q1. You are developing a SQL Server Integration Services (SSIS) project with multiple packages to copy data to a Windows Azure SQL Database database. 

An automated process must validate all related Environment references, parameter data types, package references, and referenced assemblies. The automated process must run on a regular schedule. 

You need to establish the automated validation process by using the least amount of administrative effort. 

What should you do? 

A. Use an event handler for OnError for the package. 

B. Use an event handler for OnError for each data flow task. 

C. Use an event handler for OnTaskFailed for the package. 

D. View the job history for the SQL Server Agent job. 

E. View the All Messages subsection of the All Executions report for the package. 

F. Store the System::SourceID variable in the custom log table. 

G. Store the System::ServerExecutionID variable in the custom log table. 

H. Store the System::ExecutionInstanceGUID variable in the custom log table. 

I. Enable the SSIS log provider for SQL Server for OnError in the package control flow. 

J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow. 

K. Deploy the project by using dtutil.exe with the /COPY DTS option. 

L. Deploy the project by using dtutil.exe with the /COPY SQL option. 

M. Deploy the .ispac file by using the Integration Services Deployment Wizard. 

N. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure. 

O. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure. 

P. Create a SQL Server Agent job to execute the SSISDB.catalog.create_execution and SSISDB.catalog.start_execution stored procedures. 

Q. Create a table to store error information. Create an error output on each data flow destination that writes OnError event text to the table. 

R. Create a table to store error information. Create an error output on each data flow destination that writes OnTaskFailed event text to the table. 

View Answer

Q2. You manage a SQL Server Master Data Services (MDS) environment. 

A new application requires access to the product data that is available in the MDS repository. 

You need to design a solution that gives the application access to the product data with the least amount of development effort. 

What should you do? 

A. Use sp_addlinkedserver to add a linked server to access the MDS database tables directly. 

B. Create an OLE DB connection string that sets the Provider property to MDS. 

C. Use transactional replication for data synchronization. 

D. Create a Subscription View in MDS. 

View Answer

Q3. You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS). 

You need to complete the post-installation configuration. 

What should you do? 

A. Install the Analysis Services OLE DB Provider. 

B. Make the data available for DQS operations. 

C. Run the Data Quality Server Installer. 

D. Install ADOMD.NET. 

View Answer

Q4. You are using the Knowledge Discovery feature of the Data Quality Services (DQS) client application to modify an existing knowledge base. 

In the mapping configuration, two of the three columns are mapped to existing domains in the knowledge base. The third column, named Group, does not yet have a domain. 

You need to complete the mapping of the Group column. 

What should you do? 

A. Map a composite domain to the source column. 

B. Create a composite domain that includes the Group column. 

C. Add a domain for the Group column. 

D. Add a column mapping for the Group column. 

View Answer

Q5. You are developing a SQL Server Integration Services (SSIS) package. An Execute SQL 

task in the package checks product stock levels and sets a package variable named InStock to TRUE or FALSE depending on the stock level found. 

After the successful execution of the Execute SQL task, one of two data flow tasks must run, depending on the value of the InStock variable. 

You need to set the precedence constraints. 

Which value for the evaluation operation should you use? (To answer, select the appropriate option for the evaluation operation in the answer area.) 

View Answer

Q6. 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. Natural 

B. Explicit 

C. Parent 

D. Recursive 

View Answer

Q7. You develop a SQL Server Integration Services (SSIS) package in a project by using the Project Deployment Model. It is regularly executed within a multi-step SQL Server Agent job. 

You make changes to the package that should improve performance. 

You need to establish if there is a trend in the durations of the next 10 successful executions of the package. You need to use the least amount of administrative effort to achieve this goal. 

What should you do? 

A. After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the package. 

B. Configure the package to send you an email upon completion that includes information about the duration of the package. After 10 executions, view the emails. 

C. Enable logging to the Application Event Log in the package control flow for the OnInformation event. After 10 executions, view the Application Event Log. 

D. Enable logging to the Application Event Log in the package control flow for the OnPostExecute event. After 10 executions, view the Application Event Log. 

View Answer

Q8. You are designing a SQL Server Integration Services (SSIS) package configuration strategy. 

The package configuration must meet the following requirements: Include multiple properties in a configuration. Force packages to load all settings in the configuration. Support Encrypting File System (EFS) formats. You need to select the appropriate configuration. 

Which configuration type should you use? 

To answer, select the appropriate option from the drop-down list in the dialog box. 

View Answer

Q9. To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a logging methodology is put in place. 

The methodology has the following requirements: 

The deployment process must be simplified. 

All the logs must be centralized in SQL Server. 

Log data must be available via reports or T-SQL. 

Log archival must be automated. 

You need to configure a logging methodology that meets the requirements while minimizing the amount of deployment and development effort. 

What should you do? 

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

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

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

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

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

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

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

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

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

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

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

View Answer

Q10. You are the Master Data Services (MDS) administrator at your company. 

An existing user must be denied access to a certain hierarchy node for an existing model. 

You need to configure the user's permissions. 

Which user management menu item should you select? (To answer, configure the 

appropriate option or options in the dialog box in the answer area.) 

View Answer