16 tips on How to 70-463 Test Like a Badass [161 to 176]

70-463 Royal Pack Testengine pdf

100% Actual & Verified — 100% PASS

Unlimited access to the world's largest Dumps library! Try it Free Today!

Get it now →

Product Description:
Exam Number/Code: 70-463
Exam name: Implementing a Data Warehouse with Microsoft SQL Server 2012
n questions with full explanations
Certification: Microsoft Certification
Last updated on Global synchronizing

Free Certification Real IT 70-463 Exam pdf Collection

Question No. 161

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? 

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.va!idate_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 OnTaskFailed event text to the table. 

Answer:


Question No. 162

A SQL Server Integration Services (SSIS) package named DataFeed interacts with an external vendor data feed. The package is executed several times a day, either as part of other packages' control flow or by itself. The external data feed is unreliable because network failures and slow response times are frequent. The package is currently deployed on the file system. 

To analyze the reliability of the external data feed, you must collect execution data. 

Every time the DataFeed package is executed, the following information must be logged: 

Start Time 

End Time 

Execution Result 

Execution Duration 

You need to design a logging solution that meets the requirements by using the least amount of administrative and development effort. 

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


Answer: 



Question No. 163

You administer a SQL Server Integration Services (SSIS) solution in the SSIS catalog. A SQL Server Agent job is used to execute a package daily with the basic logging level. 

Recently, the package execution failed because of a primary key violation when the package inserted data into the destination table. 

You need to identify all previous times that the package execution failed because of a primary key violation. 

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:


Question No. 164

You are using the Knowledge Discovery feature of the Data Quality Services (DQS) client application to modify an existing knowledge base. 

In the mapping configuration, two of the three columns are mapped to existing domains in the knowledge base. The third column, named Team Type, does not yet have a domain. 

You need to complete the mapping of the Team Type column. 

What should you do? 

A. Add a column mapping for the Team Type column. 

B. Map a composite domain to the source column. 

C. Create a composite domain that includes the Team Type column. 

D. Add a domain for the Team Type column. 

Answer:

Explanation: References: http://msdn.microsoft.com/en-us/library/ff877917.aspx 

http://msdn.microsoft.com/en-us/library/hh213015.aspx http://msdn.microsoft.com/en-us/library/gg524799.aspx 


Question No. 165

A Data Flow task in a SQL Server Integration Services (SSIS) package produces run-time errors. 

You need to edit the package to log specific error messages. 

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


Answer: 



70-463 practice exam

Question No. 166

You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model. 

The project will be deployed to an SSIS catalog folder where Environments have already been created. You need to deploy the project. 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:


Question No. 167

You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse hosted on SQL Azure. 

The package uses a Foreach container to process text files found in a folder. The package must be deployed to a single server by using the Project Deployment model. 

Multiple SQL Server Agent jobs call the package. Each job is executed on a different schedule. Each job passes a different folder path to the package. 

You need to configure the package to accept the folder path from each job. 

Which package configuration should you use? 

A. .dtsConfig file 

B. Registry Entry 

C. Environment Variable 

D. Parent Package Variable 

E. XML Configuration File 

Answer:

Reference: http://msdn.microsoft.com/en-us/library/ms345179.aspx 

http://msdn.microsoft.com/en-us/library/ms141708.aspx http://msdn.microsoft.com/en-us/library/hh213214.aspx http://msdn.microsoft.com/en-us/library/hh213296.aspx http://msdn.microsoft.com/en-us/library/hh213293.aspx 


Question No. 168

You are designing an extract, transform, and load (ETL) solution that loads data into dimension tables. The ETL process involves many transformation steps. 

You need to ensure that the design can provide: 

Auditing information for compliance and business user acceptance Tracking and unique identification of records for troubleshooting and error correction 

What should you do? 

A. Create a SQL Common Language Runtime (SQLCLR) component that records all 

transformation steps in a Microsoft SharePoint list. 

B. Create a version control repository for the transformation steps in Team Foundation Server (TFS). 

C. Develop a custom data lineage solution. 

D. Develop a Data Quality Services (DQS) solution. 

Answer:


Question No. 169

You are editing a SQL Server Integration Services (SSIS) package that uses checkpoints. 

The package performs the following steps: 

1. Download a sales transaction file by using FTP. 

2. Truncate a staging table. 

3. Load the contents of the file to the staging table. 

4. Merge the data with another data source for loading to a data warehouse. 


The checkpoints are currently working such that if any of the four steps fail, the package will restart from the failed step the next time it executes. 

You need to modify the package to ensure that if either the Truncate Staging Table or the Load Sales to Staging task fails, the package will always restart from the Truncate Staging Table task the next time the package runs. 

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: 



Question No. 170

You develop a SQL Server Integration Services (SSIS) project by using the Project Deployment model. 

The project contains many packages. It is deployed on a server named Development!. The project will be deployed to several servers that run SQL Server 2012. 

The project accepts one required parameter. The data type of the parameter is a string. 

A SQL Agent job is created that will call the master.dtsx package in the project. A job step is created for the SSIS package. 

The job must pass the value of an SSIS Environment Variable to the project parameter. The value of the Environment Variable must be configured differently on each server that runs SQL Server. The value of the Environment Variable must provide the server name to the project parameter. 

You need to configure SSIS on the Development1 server to pass the Environment Variable to the package. 

Which four actions should you perform in sequence by using SQL Server Management Studio? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.) 


Answer: 



70-463 rapidshare

Question No. 171

You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD). 

You need to add a task or component to the package that allows you to implement the SCD logic. 

What should you use? 

A. a Script component 

B. an SCD component 

C. an Aggregate component 

D. a Merge component 

Answer:


Question No. 172

You are the Master Data Services (MDS) administrator at your company. 

An existing user must be denied access to a certain hierarchy node for an existing model. 

You need to configure the user's permissions. 

Which user management menu item should you select? (To answer, configure the 

appropriate option or options in the dialog box in the answer area.) 


Answer: 



Question No. 173

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 custom .NET application based on the Knowledgebase class. 

C. Create a data quality project. 

D. Create a CLR stored procedure based on the Knowledgebase class. 

E. Create a Master Data Services (MDS) business rule. 

Answer:

Explanation: References: http://msdn.microsoft.com/en-us/library/hh213052.aspx 

http://msdn.microsoft.com/en-us/library/ff877917.aspx http://msdn.microsoft.com/en-us/library/microsoft.masterdataservices.services.datacontracts.knowledgebase.aspx 

http://msdn.microsoft.com/en-us/library/bb895263.aspx 


Question No. 174

A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse. 

The SSIS catalog includes two Environments: 

. Development 

. QA 

Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the development or QA data warehouses. 

You need to be able to execute deployed packages by using either of the defined Environments. 

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


Answer: 



Question No. 175

You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure database into a data warehouse. 

The source system contains redundant or inconsistent data. When the package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a text file for further analysis. 

You need to establish the best technique to log these invalid rows while keeping the amount of development effort to a minimum. 

What should you do? 

A. Add an OnError event handler to the SSIS project. 

B. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility. 

C. Use an msi file to deploy the package on the server. 

D. Open a command prompt and run the gacutil command. 

E. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server. 

F. Open a command prompt and run the dtutil /copy command. 

G. Create a reusable custom logging component and use it in the SSIS project. 

H. Configure the SSIS solution to use the Project Deployment Model. 

I. Configure the output of a component in the package data flow to use a data tap. 

J. Open a command prompt and run the dtexec /rep /conn command. 

K. Open a command prompt and run the dtexec /dumperror /conn command. 

Answer:

Explanation: 

References: http://technet.microsoft.com/en-us/library/hh230989.aspx 

http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/ms162820.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx http://technet.microsoft.com/en-us/library/ms140223.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx 


Question No. 176

You develop a SQL Server Integration Services (SSIS) package that imports Windows Azure SQL Database data into a data warehouse every night. 

The Windows Azure SQL Database data contains many misspellings and variations of abbreviations. To import the data, a developer used the Fuzzy Lookup transformation to choose the closest-matching string from a reference table of allowed values. The number of rows in the reference table is very large. 

If no acceptable match is found, the Fuzzy Lookup transformation passes a null value. 

The current setting for the Fuzzy Lookup similarity threshold is 0.50. 

Many values are incorrectly matched. 

You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading performance. 

What should you do? 

A. Change the similarity threshold to 0.40. 

B. Decrease the maximum number of matches per lookup. 

C. Change the similarity threshold to 0.85. 

D. Increase the maximum number of matches per lookup. 

Answer:

Explanation: * Similarity threshold Set the similarity threshold at the component level by using the slider. The closer the value is to 1, the closer the resemblance of the lookup value to the source value must be to qualify as a match. Increasing the threshold can improve the speed of matching since fewer candidate records need to be considered. 

Incorrect: 

* Maximum number of matches to output per lookup 

Specify the maximum number of matches the transformation can return for each input row. 

The default is 1.