70-463 Free Practice Test

You are developing a SQL Server Integration Services (SSIS) package to load data into a Windows Azure SQL Database database. The package consists of several data flow tasks.
The package has the following auditing requirements:
If a data flow task fails, a Transact-SQL (T-SQL) script must be executed.
The T-SQL script must be executed only once per data flow task that fails, regardless of the nature of the error.
You need to ensure that auditing is configured to meet these requirements. What should you do?

1. A. Use an event handler for OnError for the package.
2. B. Use an event handler for OnError for each data flow task.
3. C. Use an event handler for OnTaskFailed for the package.
4. D. View the job history for the SQL Server Agent job.
5. E. View the All Messages subsection of the All Executions report for the package.
6. F. Store the System::SourceID variable in the custom log table.
7. G. Store the System::ServerExecutionID variable in the custom log table.
8. H. Store the System::ExecutionInstanceGUID variable in the custom log table.
9. I. Enable the SSIS log provider for SQL Server for OnError in the package control flow.
10. J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.
11. K. Deploy the project by using dtutil.exe with the /COPY DTS option.
12. L. Deploy the project by using dtutil.exe with the /COPY SQL option.
13. M. Deploy the .ispac file by using the Integration Services Deployment Wizard.
14. N. Create a SQL Server Agent job to execute the SSISDB.catalog.va!idate_project stored procedure.
15. O. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
16. P. Create a SQL Server Agent job to execute theSSISDB.catalog.create_execution and SSISDB.catalog.start_execution stored procedures.
17. Q. Create a table to store error informatio
18. R. Create an error output on each data flow destination that writes OnTaskFailed event text to the table.

Correct Answer: C

DRAG DROP
You are loading a dataset into SQL Server. The dataset contains numerous duplicates for the Artist and Song columns.
The values in the Artist column in the dataset must exactly match the values in the Artist domain in the knowledge base. The values in the Song column in the dataset can be a close match with the values in the Song domain.
You need to use SQL Server Data Quality Services (DQS) to define a matching policy rule to identify duplicates.
How should you configure the Rule Editor? (To answer, drag the appropriate answers to the answer area.)

Solution:


Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages.
All packages in the project must connect to the same database. The server name for the database must be set by using a parameter named ServerParam when any package in the project is executed. You need to develop this project with the least amount of development effort.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

1. A. Set the Sensitive property of the parameter to True.
2. B. Edit each package Connection Manage
3. C. Set the ServerName property to@[$Project::ServerParam].
4. D. Edit the project Connection Manager in Solution Explore
5. E. Set the ServerName property to@[$Project::ServerParam].
6. F. Create a project parameter named ServerName.
7. G. Create a package parameter named ServerName in each package.
8. H. Set the Required property of the parameter to True.

Correct Answer: CDF
C: From question: "
The server name for the database must be set by using a parameter named ServerParam when any package in the project is executed."
D: SSIS 2012 has introduced the concept of Project level connection managers. An SSIS project is generally more than one package. To simplify lives, the SSIS team now allows for the sharing of common resources across projects, connection managers being one of those resources.
F: When a parameter is marked as required, a server value or execution value must be specified for that parameter. Otherwise, the corresponding package does not execute. Although the parameter has a default value at design time, it will never be used once the project is deployed.
Note:
* Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package
parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package.
Reference: Integration Services (SSIS) Parameters

DRAG DROP
You are designing an extract, transform, load (ETL) process with SQL Server Integration Services (SSIS). Two packages, Package A and Package B, will be designed. Package A will execute Package B. Both packages must reference a file path corresponding to an input folder where files will be located for further processing.
You need to design a solution so that the file path can be easily configured with the least administrative and development effort.
Which four 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.)

Solution:
Reference:
http://msdn.microsoft.com/en-us/library/hh479588.aspx http://msdn.microsoft.com/en-us/library/hh213290.aspx http://msdn.microsoft.com/en-us/library/hh213373.aspx

Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

DRAG DROP
You have a data warehouse.
You need to create a SQL Server Integration Services (SSIS) package to meet the following requirements:
-The package must be deployed.
-The package must be parameterized.
-The package execution must be automated.
In which sequence should you perform the actions? To answer, move all of the actions from the list of actions to the answer area and arrange them in the correct order.

Solution:


Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A