Online Access Free 70-458 Exam Questions

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.add_data_tap stored procedure with the package execution_id.
• B.Execute the catalog.create_execution_dump stored procedure with the package execution_id.
• C.Run the DTEXEC utility with the /Logger option.
• D.Run the DTEXEC utility with the /Reporting V option.

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 Environments in the SSIS catalog of the host SQL Server SSIS instance. Select the appropriate Environment at package execution time.
• B.For each target server, create a separate XML configuration file. Select the XML configuration file at package execution time.
• C.For each target server, create separate registry entry configurations. Select the registry entry at package execution time.
• D.Create one SSIS catalog Environment. Change the values of each Environment variable at package execution time.

A SQL Server Integration Services (SSIS) package imports daily transactions from several files into a SQL Server table named Transaction. Each file corresponds to a different store and is imported in parallel with the other files. The data flow tasks use OLE DB destinations in fast load data access mode.
The number of daily transactions per store can be very large and is growing. The Transaction table does not have any indexes.
You need to minimize the package execution time.
What should you do?

• A.Run the package in Performance mode.
• B.Partition the table by day and store.
• C.Reduce the value of the Maximum Insert Commit Size property.
• D.Create a clustered index on the Transaction table.

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

You administer two Microsoft SQL Server 2012 servers named ProdSrv1 and ProdSrv2. ProdSrv1 is configured as a Distributor. Both servers are configured to use the windows NT Service virtual accounts for all SQL Services.
You are configuring snapshot replication from ProdSrv1 to ProdSrv2 by using ProdSrv2 as a pull subscriber.
The distribution agent on ProdSrv2 regularly fails, displaying the following error message:
"Cannot access the file. Operating system error code 5 (Access is denied.)."
You need to configure the distribution agent by granting only the minimum required access to all accounts.
What should you do?

• A.Configure the Subscriber to use a Windows domain account. Grant READ access for the domain account to the ReplData share on ProdSrv1.
• B.Configure the Subscriber to use the Local System account.
• C.Configure the SQL Server Agent service to run under a Windows domain account. Configure the Subscriber to use the SQL Server Agent service account. Grant FULL CONTROL access for the domain account to the ReplData share on ProdSrv1.
• D.Configure the SQL Server Agent service to run under the Local System account. Configure the Subscriber to use the SQL Server Agent service account.