Online Access Free 070-463 Practice Test

You are developing a SQL Server Integration Services (SSIS) package.
The package uses custom functionality that accesses a SQL Server database. The custom functionality must be implemented by using Language Integrated Query (LINQ).
You need to ensure that the LINQ code can be debugged at design time.
What should you select from the SSIS Toolbox? (To answer, drag the appropriate item to the correct location in the answer area.)

Correct Answer:

Explanation

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.create_environment_reference
• B.catalog.set_environment_property
• C.catalog.set_environment_reference_type
• D.catalog.create_environment

You are the data steward for a Business Intelligence project.
You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.
You need to produce the CSV file with the least amount of development effort.
What should you do?

• A.Create a data quality project.
• B.Create an Integration Services package and use a Data Profiling transform.
• C.Create a CLR stored procedure based on the Knowledgebase class.
• D.Create a Master Data Services (MDS) business rule.
• E.Create a custom .NET application based on the Knowledgebase class.

You are designing a SQL Server Integration Services (SSIS) package to execute 12 Transact-SQL (T-SQL) statements on a SQL Azure database. The T-SQL statements may be executed in any order. The T-SQL statements have unpredictable execution times.
You have the following requirements:
*The package must maximize parallel processing of the T-SQL statements.
*After all the T-SQL statements have completed, a Send Mail task must notify administrators.
You need to design the SSIS package. 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:

Explanation
Box 1:

Box 2:

Box 3:

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