Free Microsoft 70-458 Exam Dumps Questions & Answers

You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads data from a source system that uses the SQL Server change data capture (CDC) feature.
You have added a CDC Source component to the data flow to read changed data from the source system.
You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations.
Which data flow transformation should you use?

• A.  CDC Splitter
• B.  Merge Join
• C.  Pivot
• D.  DQS Cleansing

DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package.
The package contains several tasks that must repeat until an expression evaluates to
FALSE.
You need to add and configure a container to enable this design.
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:

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
To ease the debugging of packages, you standardize the SQL Server Integration Services (SSIS) package logging methodology.
The methodology has the following requirements:
Centralized logging in SQL Server
Simple deployment
Availability of log information through reports orT-SQL
Automatic purge of older log entries
Configurable log details
-----
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.  use the dtutil /copy command.
• B.  Add a data tap on the output of a component in the package data flow.
• C.  Run the package by using the dtexecui.exe utility and the SQL Log provider.
• D.  use the Project Deployment Wizard.
• E.  create a reusable custom logging component.
• F.  Run the package by using the dtexec /dumperror /conn command.
• G.  Deploy the package by using an msi file.
• H.  create an OnError event handler.
• I.  Use the gacutil command.
• J.  Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
• K.  Run the package by using the dtexec /rep /conn command.

DRAG DROP
You administer a Microsoft SQL Server 2012 database. You use an OrderDetail table that has the following definition:

You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the CarrierTrackingNumber column.
Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

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