P2 68-157

QUESTION 68 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 correct answer presents a complete solution. Choose all that apply.)

QUESTION 85 You are developing a SQL Server Integration Services (SSIS) project that copies a large amount of rows from a SQL Azure database. The project uses the Package Deployment Model. This project is deployed to SQL Server on a test server. You need to ensure that the project is deployed to the SSIS catalog on the production server. What should you do?

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

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

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

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

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

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

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

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

I. Configure the SSIS Solution to use the Project Deployment Model.

QUESTION 35 You are designing an extract, transform, load (ETL) process for loading data from a SQL Server database into a large fact table in a data warehouse each day with the prior day's sales data. The ETL process for the fact table must meet the following requirements:  Load new data in the shortest possible time.  Remove data that is more than 36 months old.  Ensure that data loads correctly.  Minimize record locking.  Minimize impact on the transaction log. You need to design an ETL process that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

A. Partition the destination fact table by date. Insert new data directly into the fact table and delete old data directly from the fact table.

B. Partition the destination fact table by date. Use partition switching and staging tables both to remove old data and to load new data.

C. Partition the destination fact table by customer. Use partition switching both to remove old data and to load new data into each partition.

D. Partition the destination fact table by date. Use partition switching and a staging table to remove old data. Insert new data directly into the fact table.

QUESTION 127 You install a SQL Server 2012 database engine instance on a production server. A month later, you install SQL Server 2012 Integration Services (SSIS). You must develop an SSIS project and deploy it to the server by using the Project Deployment model. Operations log records that are outside the configured retention period must be cleaned automatically. You need to create the SSIS catalog on the production server and ensure that the operations log cleaning requirement is met. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

A. Enable CLR Integration.

B. Enable FILESTREAM with Full Access.

C. Enable the Resource Governor.

D. Change the recovery mode of the msdb database to FULL.

E. Change the Server-wide Default Logging Level in SSISDB to Verbose.

F. Start the SQL Server Browser service.

G. Start the SQL Server Agent service.

QUESTION 145 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.

QUESTION 149 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.

QUESTION 152 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.

QUESTION 155 You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model. The project is deployed to a single SSIS catalog, and transfers data to and from multiple databases hosted on SQL Server. The project must be configured to be able to export data to and from five different production servers that run SQL Server 2012. Each target server requires different values for connection strings and parameters in the SSIS project. You need to meet the requirements by using the least amount of administrative effort. What should you do?

A. For each target server, create separate registry entry configurations. Select the registry entry at package execution time.

B. For each target server, create separate Environments in the SSIS catalog of the host SQL Server SSIS instance. Select the appropriate Environment at package execution time.

C. Create one SSIS catalog Environment. Change the values of each Environment variable at package execution time.

D. For each target server, create a separate XML configuration file. Select the XML configuration file at package execution time.

QUESTION 157 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. Edit each package Connection Manager. Set the ServerName property to @[$Project::ServerParam].

C. Edit the project Connection Manager in Solution Explorer. Set the ServerName property to @[$Project::ServerParam].

D. Create a project parameter named ServerName.

E. Create a package parameter named ServerName in each package.

F. Set the Required property of the parameter to True.

Unlock and Upgrade

Remove all limits from your Quizzes

P2 68-157

More Quizzes