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

Q2. You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model. All packages in the project must log custom messages. 

You need to produce reports that combine the custom log messages with the system-generated log messages. 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

Q3. You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. You add an Execute SQL task to the control flow. 

The task must execute a simple INSERT statement. The task has the following requirements: 

. The INSERT statement must use the value of a string package variable. The variable name is StringVar. . The Execute SQL task must use an OLE DB Connection Manager. 

In the Parameter Mapping tab of the Execute SQL task, StringVar has been added as the only parameter. 

You must configure the SQLStatement property of the Execute SQL task. 

Which SQL statement should you use? 

A. INSERT INTO dbo.Table (variablevalue) VALUES ($StringVar) 

B. INSERT INTO dbo.Table (variablevalue) VALUES (0) 

C. INSERT INTO dbo.Table (variablevalue) VALUES (@0) 

D. INSERT INTO dbo.Table (variablevalue) VALUES (?) 

View Answer

Q4. You are implementing a new SQL Server Integration Services (SSIS) 2012 package that loads data from various flat files and a Windows Azure SQL Database database. 

Daily transactions must be loaded into a staging database. All the SSIS tasks will use the CurrentDate variable as the transaction date. 

You need to set the CurrentDate variable to the current execution date when the package starts. You need to achieve this goal by using the least amount of development effort. 

What should you use to set the variable? 

A. An Expression task 

B. A Script component 

C. A Script task 

D. An Execute SQL task 

View Answer

Q5. You are deploying a new SQL Server Integration Services (SSIS) package to several servers. 

The package must meet the following requirements: 

. .NET Common Language Runtime (CLR) integration in SQL Server must not be enabled. 

. The Connection Managers used in the package must be configurable without editing the package. 

. The deployment procedure must be automated as much as possible. 

You need to set up a deployment strategy that meets the requirements. 

What should you do? 

A. Use the gacutil command. 

B. Use the dtutil /copy command. 

C. Use the Project Deployment Wizard. 

D. Create an OnError event handler. 

E. Create a reusable custom logging component. 

F. Run the package by using the dtexec /rep /conn command. 

G. Run the package by using the dtexec /dumperror /conn command. 

H. Run the package by using the dtexecui.exe utility and the SQL Log provider. 

I. Add a data tap on the output of a component in the package data flow. 

J. Deploy the package by using an msi file. 

K. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration. 

View Answer

Q6. A SQL Server Integration Services (SSIS) package on a computer is failing periodically in production. The package was created less than one year ago and was deployed to the SSIS catalog. 

Sometimes the package is started on a SQL Agent schedule; sometimes the package is started manually by an SSIS developer 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 failed in the past. 

Where can you find this information? 

A. the SQL Server Log 

B. the SSISDB.[catalog].[executions] view 

C. the SSISDB.[catalog].[event_messages] view 

D. the SQL Agent Job History 

E. the SQL Agent Error Log 

View Answer

Q7. A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse. 

The SSIS catalog includes two Environments: 

. Test 

. Production 

Each Environment defines a single Environment Variable named ConnectionString of type 

string. The value of each variable consists of the connection string to the test or production data warehouses. 

You need to execute deployed packages by using either of the defined Environments. 

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

Q8. 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. Create a Subscription View in MDS. 

B. Access the product entity tables in the MDS database directly. 

C. Use SQL Server Integration Services (SSIS) to extract the data and put it in a staging database. 

D. Use change data capture on the product entity tables. 

View Answer

Q9. You are building a SQL Server Integration Services (SSIS) package to load product data sourced from a SQL Azure database to a data warehouse. Before the product data is loaded, you create a batch record by using an Execute SQL task named Create Batch. After successfully loading the product data, you use another Execute SQL task named Set Batch Success to mark the batch as successful. 

You need to create and execute an Execute SQL task to mark the batch as failed if either the Create Batch or Load Products task fails. 

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

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