Online Access Free 070-463 Practice Test

You have a server named SQL1 that has SQL Server Integration Services (SSIS) installed. SQL1 has eight CPUs, 8 GB of RAM, RAID-10 storage, and a 10-gigabit Ethernet connection.
A package named Package1 runs on SQL1. Package1 contains 10 data tasks. Package1 queries 3 GB of data from the source system.
You review the performance statistics on SQL1 and discover that Package1 executes slower than expected.
You need to identify the problem that is causing Package1 to execute slowly.
Which performance monitor counter data should you review?

• A.Processor\% Idle Time
• B.Processor\% User Time
• C.SQLServer:SSIS Pipeline 10.0:Buffers in use
• D.Memory\Pages/sec

You are developing a SQL Server Integration Services (SSIS) package that loads data from a source transactional tables in a data warehouse.
You discover that one of the data flows loads incorrect data to one of the destination tables.
You need to identify the cause of the incorrect data flow. Which tool should you use?

• A.SSIS logging.
• B.the locals window.
• C.a breakpoint
• D.a data viewer

You are using a SQL Server Integration Services (SSIS) project that is stored in the SSIS catalog. An Environment has been defined in the SSIS catalog.
You need to add the Environment to the project.
Which stored procedure should you use?

• A.catalog.set_environment_variable_value
• B.catalog.create_environment_variable
• C.catalog.set_execution_parameter_value
• D.catalog.create_environment_reference

You plan to design an SQL Server Integration Services (SSIS) package to move data from a source system to an SQL Server database named Warehouse.
You need to implement a data loading strategy for the package. The solution must ensure that if a failure occurs, the connection automatically resumes from the point of failure.
How should you configure each package property? To answer, select the appropriate options in the answer area.

Correct Answer:

Explanation

Box 1 CheckPointUsage: Always
CheckpointUsage specifies whether checkpoints are used.
Always specifies that the checkpoint file is always used and that the package restarts from the point of the previous execution failure. If the checkpoint file is not found, the package fails.
Box 2: FailPackageOnFailure: False
You must set the FailPackageOnFailure property to true for all the containers in the package that you want to identify as restart points.
Box 3: SaveCheckPoints: Yes
SaveCheckpoints indicates whether the package saves checkpoints. This property must be set to True to restart a package from a point of failure.
Box 4: TransactionOption: Supported
TransactionOption in SSIS is used to start transactions, add executables to transactions or keep them out of transactions. It has 3 values; namely
-> Required :- The container which has the transactionoption set to this, joins any available transaction or else starts a new transaction .
-> Supported -> Joins any available transaction (started by its parent container) but if not available, does not start one.
-> Not Supported -> Does not support transactions.
The transactions follow all the
References:
https://docs.microsoft.com/en-us/sql/integration-services/packages/restart-packages-by-using-checkpoints?view

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.Create an OnError event handler.
• B.Deploy the package by using an msi file.
• C.Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
• D.Create a reusable custom logging component.
• E.Run the package by using the dtexec /dumperror /conn command.
• F.Run the package by using the dtexecui.exe utility and the SQL Log provider.
• G.Use the gacutil command.
• H.Run the package by using the dtexec /rep /conn command.
• I.Use the Project Deployment Wizard.
• J.Add a data tap on the output of a component in the package data flow.
• K.Use the dtutil /copy command.