Free Microsoft 070-458 Exam Dumps Questions & Answers

DRAG DROP
---
You administer a Microsoft SQL Server 2012 database.
The database is backed up according to the following schedule:
Daily full backup at 23:00 hours.
Differential backups on the hour, except at 23:00 hours.
Log backups every 10 minutes from the hour, except on the hour.
The database uses the Full recovery model.
A developer accidentally drops a number of tables and stored procedures from the database between 22:40 hours and 23:10 hours. You perform a database restore at 23:30 hours to recover the dropped table.
You need to restore the database by using the minimum amount of administrative effort. You also need to ensure minimal data loss.
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.)

Correct Answer:

You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment model. A package in the project extracts data from a Windows Azure SQL Database database. The package is deployed to SQL Server.
The package is not producing the desired results.
You need to generate the .mdmp and .tmp debug files in order to troubleshoot the issues.
What should you do?

• A.  Execute the catalog.create_execution_dump stored procedure with the package execution_id.
• B.  Run the DTEXEC utility with the /Logger option.
• C.  Run the DTEXEC utility with the /Reporting V option.
• D.  Execute the catalog.add_data_tap stored procedure with the package execution_id.

HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads rows from a source system and compares them to rows in a destination system. New rows will be inserted and changed rows will be updated.
You have used a Lookup transformation and a Conditional Split transformation. The Lookup transformation joins the source and destination table on the business key, and includes all columns from the destination table in the data flow output. The Conditional Split transformation inspects the destination columns and directs data flow to either insert new records or update existing records.
You need to configure the Lookup transformation to ensure that all records flow to the Conditional Split transformation, regardless of whether the rows match an existing row in the destination table.
Which setting should you select? (To answer, select the appropriate option in the answer area.)

Correct Answer:

You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse. The package consists of several data flow tasks.
The package experiences intermittent errors in the data flow tasks.
If any data flow task fails, all package error information must be captured and written to a SQL Server table by using an OLE DB connection manager.
You need to ensure that the package error information is captured and written to the table.
What should you do?

• A.  Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.
• B.  Deploy the project by using dtutil.exe with the /COPY SQL option.
• C.  Store the System::SourceID variable in the custom log table.
• D.  Use an event handler for OnError for the package.
• E.  Store the System::ServerExecutionID variable in the custom log table.
• F.  Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.
• G.  Use an event handler for OnError for each data flow task.
• H.  View the All Messages subsection of the All Executions report for the package.
• I.  Use an event handler for OnTaskFailed for the package.
• J.  Create a table to store error information. Create an error output on each data flow destination that writes OnError event text to the table.
• K.  Deploy the .ispac file by using the Integration Services Deployment Wizard.
• L.  Store the System::ExecutionInstanceGUID variable in the custom log table.
• M.  Deploy the project by using dtutil.exe with the /COPY DTS option.
• N.  View the job history for the SQL Server Agent job.
• O.  Enable the SSIS log provider for SQL Server for OnError in the package control flow.
• P.  Create a SQL Server Agent job to execute the SSISDB.catalog.create_execution and SSISDB.catalog.*tart_execution stored procedures.
• Q.  Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
• 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.

A SQL Server Integration Services (SSIS) 2012 package currently downloads sales data from a Windows Azure SQL Database database.
To improve sales data accuracy, exchange rates must be downloaded daily from a public HTTP web service instead of from a weekly flat file.
You need to implement the change to the existing package while minimizing the development effort.
What should you use to call the web service to retrieve the daily exchange rates?

• A.  a Web Service task
• B.  a Script component
• C.  a Web Service source
• D.  a Script task