70-463 Exam Questions - Online Test

70-463 Premium VCE File

Learn More 100% Pass Guarantee - Dumps Verified - Instant Download
150 Lectures, 20 Hours

Your success in Microsoft exam 70 463 is our sole target and we develop all our 70 463 training kit pdf braindumps in a way that facilitates the attainment of this target. Not only is our exam 70 463 study material the best you can find, it is also the most detailed and the most updated. 70 463 dumps Practice Exams for Microsoft SQL Server 70 463 training kit pdf are written to the highest standards of technical accuracy.

Q71. You are developing a SQL Server Integration Services (SSIS) project with multiple packages to copy data to a Windows Azure SQL Database database. 

An automated process must validate all related Environment references, parameter data types, package references, and referenced assemblies. The automated process must run on a regular schedule. 

You need to establish the automated validation process by using the least amount of administrative effort. 

What should you do? 

A. Use an event handler for OnError for the package. 

B. Use an event handler for OnError for each data flow task. 

C. Use an event handler for OnTaskFailed for the package. 

D. View the job history for the SQL Server Agent job. 

E. View the All Messages subsection of the All Executions report for the package. 

F. Store the System::SourceID variable in the custom log table. 

G. Store the System::ServerExecutionID variable in the custom log table. 

H. Store the System::ExecutionInstanceGUID variable in the custom log table. 

I. Enable the SSIS log provider for SQL Server for OnError in the package control flow. 

J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow. 

K. Deploy the project by using dtutil.exe with the /COPY DTS option. 

L. Deploy the project by using dtutil.exe with the /COPY SQL option. 

M. Deploy the .ispac file by using the Integration Services Deployment Wizard. 

N. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure. 

O. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure. 

P. Create a SQL Server Agent job to execute the SSISDB.catalog.create_execution and SSISDB.catalog.start_execution stored procedures. 

Q. Create a table to store error information. Create an error output on each data flow destination that writes OnError event text to the table. 

R. Create a table to store error information. Create an error output on each data flow destination that writes OnTaskFailed event text to the table. 

Answer: N 

Q72. You are designing a SQL Server Integration Services (SSIS) data flow to load sales 

transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named ProductCode. 

Some of the data to be loaded will reference products that need special processing logic in the data flow. 

You need to enable separate processing streams for a subset of rows based on the source product code. 

Which Data Flow transformation should you use? 

A. Script Component 

B. Audit 

C. Destination Assistant 

D. Data Conversion 

Answer: A 

Q73. 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 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. 

B. Use Data Flow tasks to process the clean data. 

C. Use a Data Profiling task. Read the exceptions in Data Profile Viewer. 

D. Design a stored procedure that examines data for common dirty data patterns. Use an Execute SQL task. 

Answer: C 

Q74. You install a SQL Server 2012 database engine instance on a production server. A month later, you install SQL Server 2012 Integration Services (SSIS). 

You must develop an SSIS project and deploy it to the server by using the Project Deployment model. 

Operations log records that are outside the configured retention period must be cleaned automatically. 

You need to create the SSIS catalog on the production server and ensure that the operations log cleaning requirement is met. 

What should you do? (Each correct answer presents part of the solution. Choose all that apply.) 

A. Enable CLR Integration. 

B. Enable FILESTREAM with Full Access. 

C. Enable the Resource Governor. 

D. Change the recovery mode of the msdb database to FULL. 

E. Change the Server-wide Default Logging Level in SSISDB to Verbose. 

F. Start the SQL Server Browser service. 

G. Start the SQL Server Agent service. 

Answer: A,D 

Q75. You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure. 

Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart. 

You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented. 

What should you do? 

A. Create a junk dimension for the geography dimension. 

B. Implement change data capture. 

C. Create a conformed dimension for the geography dimension. 

D. Create three geography dimensions. 

Answer: C 

Q76. You are installing the Data Quality Client on user desktops. 

You need to ensure that the prerequisite software components are installed. 

Which components must be present to meet this goal? (Each correct answer presents part of the solution. Choose all that apply.) 

A. SQL Server Management Studio 

B. Internet Explorer 6.0 SP1 or later 

C. Microsoft Silverlight 5 

D. .NET Framework 3.5 SP1 

E. .NET Framework 4.0 

F. Microsoft Silverlight 4 

G. SQL Server Data Tools 

Answer: B,E 

Explanation: Before installing DQS, make sure that your computer meets the minimum system requirements. 

Data Quality Client requirement: 

NET Framework 4.0 (installed during the Data Quality Client installation, if not already installed) 

Internet Explorer 6.0 SP1 or later 

Q77. You are reviewing the design of an existing fact table named factSales, which is loaded incrementally from a SQL Azure database by a SQL Server Integration Services (SSIS) package each hour. The fact table has approximately 4 billion rows and is dimensioned by product, sales date, and sales time of day. 

The database administrator is concerned about the rapid growth of the database and users experience poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses this fact table reports sales by product name, sale month, and sale year. No other reports will be created against this table. 

You need to reduce the report processing time and minimize the growth of the database. 

What should you do? 

A. Create an indexed view over the fact table to sum orderTotal by month. 

B. Create a view over the fact table to sum orderTotal by month. 

C. Change the granularity of the fact table to month. 

D. Partition the fact table by productKey. 

Answer: B 

Q78. You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named ProductCode. 

Some of the data to be loaded will reference products that need special processing logic in the data flow. 

You need to enable separate processing streams for a subset of rows based on the source product code. 

Which data flow transformation should you use? 

A. Source Assistant 

B. Destination Assistant 

C. Audit 

D. Script Component 

Answer: D 

Explanation: Script Component Transformation: 

The transformation that uses script to extract, transform, or load data. 

Note: 

* SQL Server Integration Services provides three different types of data flow components: sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases. Transformations modify, summarize, and clean data. Destinations load data into data stores or create in-memory datasets. 

Incorrect: Not A, Not B: No Data Transforms called Source Assistant or Destination Assistant Not C: Not related to auditing 

Q79. You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named ProductCode. 

Some of the data to be loaded will reference products that need special processing logic in the data flow. 

You need to enable separate processing streams for a subset of rows based on the source product code. 

Which Data Flow transformation should you use? 

A. Multicast 

B. Conditional Split 

C. Script Task 

D. Data Conversion 

Answer: C 

Q80. You are building a SQL Server Integration Services (SSIS) package to load product data sourced from a SQL Azure database to a data warehouse. Before the product data is loaded, you create a batch record by using an Execute SQL task named Create Batch. After successfully loading the product data, you use another Execute SQL task named Set Batch Success to mark the batch as successful. 

You need to create and execute an Execute SQL task to mark the batch as failed if either the Create Batch or Load Products task fails. 

Which three steps 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.) 

Answer: