Online Access Free 070-463 Exam Questions

You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart.
You have the following requirements:
*Ensure that the process supports the creation of an exception report that details possible duplicate key values, null ratios within columns, and column-length distributions of values.
*Ensure that users can generate the exception report in an XML format.
*Use the minimum development effort.
You need to design the SSIS solution to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A.Use Data Flow tasks to process the clean data.
• B.Use a Data Profiling task. Use a Data Flow task to extract the XML output of the Data Profiling task into a SQL Server table. Query the table to view the exceptions.
• C.Design a stored procedure that examines data for common dirty data patterns. Use an Execute SQL task.
• D.Use a Data Profiling task. Read the exceptions in Data Profile Viewer.

You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The package and all three Execute SQL tasks have their TransactionOption property set to Supported.
You need to ensure that if any of the Execute SQL tasks fail, all three tasks will roll back their changes.
What should you do?

• A.Move the three Execute SQL tasks into a Sequence container.
• B.Move the three Execute SQL tasks into a Foreach Loop container.
• C.Change the TransactionOption property of all three Execute SQL tasks to Required.
• D.Change the TransactionOption property of the package to Required.

You deploy a server that has SQL Server installed.
You deploy a SQL Server Integration Services (SSIS) package to the server.
You need to automate the execution of the package. The solution must ensure that you receive a notification if the package fails to execute.
In which order should you perform all the actions? To answer, move all of the actions to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

Correct Answer:

Explanation

Here are the steps:
References:
http://www.sherweb.com/blog/how-to-set-up-database-mail-for-sql-server-job-failures/

You are designing a SQL Server Integration Services (SSIS) package that will load data from a source system to a data mart.
You need to combine conditionally the value of two project parameters to a package variable.
What should you do?

• A.Set the Expression property of the variable.
• B.Configure an event handler.
• C.Set the data type property of the variable.
• D.Modify the namespace of the variable.

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