70-463 Exam Questions - Online Test

70-463 Premium VCE File

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

Master the 70 463 dumps Implementing a Data Warehouse with Microsoft SQL Server 2012 content and be ready for exam day success quickly with this Examcollection 70 463 pdf exam guide. We guarantee it!We make it a reality and give you real 70 463 exam questions in our Microsoft 70 463 pdf braindumps.Latest 100% VALID Microsoft 70 463 exam Exam Questions Dumps at below page. You can use our Microsoft 70 463 dumps braindumps and pass your exam.

Q21. You are designing a package control flow. The package moves sales order data from a SQL Azure transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being added to the transactional database. 

The current design of the package control flow is shown in the answer area. (Click the Exhibit button.) 

The Insert New Orders Data Flow task must meet the following requirements: 

Usage of the tempdb database should not be impacted. 

Concurrency should be maximized, while only reading committed transactions. 

If the task fails, only that task needs to be rolled back. 

You need to configure the Insert New Orders Data Flow task to meet the requirements. 

How should you configure the transaction properties? To answer, select the appropriate setting or settings in the answer area. 

Select “IsolationLeve as Choas” 

Answer: 

Q22. 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: 

. Test 

. Production 

Each Environment defines a single Environment Variable named ConnectionString of type 

string. The value of each variable consists of the connection string to the test or production data warehouses. 

You need 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: 

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

Answer: 

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

The SQL Azure 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. Decrease the maximum number of matches per lookup. 

B. Change the similarity threshold to 0.55. 

C. Change the Exhaustive property to True. 

D. Increase the maximum number of matches per lookup. 

Answer: B 

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

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

You add an Execute SQL task to the control flow. The task must execute a simple INSERT statement. 

The task has the following requirements: 

. The INSERT statement must use the value of a string package variable. The variable name is StringVar. . The Execute SQL task must use an OLE DB Connection Manager. 

In the Parameter Mapping tab of the Execute SQL task, StringVar has been added as the only parameter. 

You must configure the SQLStatement property of the Execute SQL task. 

Which SQL statement should you use? 

A. INSERT INTO dbo.Table (variablevalue) VALUES (@StringVar) 

B. INSERT INTO dbo.Table (variablevalue) VALUES ($Project::StringVar) 

C. INSERT INTO dbo.Table (variablevalue) VALUES (?) 

D. INSERT INTO dbo.Table (variablevalue) VALUES ($Package::StringVar) 

Answer: C 

Q26. You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The three Execute SQL tasks modify products in staging tables in preparation for a data warehouse load. 

The package and all three Execute SQL product tasks have their TransactionOption property set to Supported. 

You need to ensure that if any of the three Execute SQL product tasks fail, all three tasks will roll back their changes. 

What should you do? 

A. Change the TransactionOption property of the package to Required. 

B. Change the TransactionOption property of all three Execute SQL product tasks to Required. 

C. Move the three Execute SQL product tasks into a Foreach Loop container. 

D. Move the three Execute SQL product tasks into a Sequence container. 

Answer: A 

Explanation: 

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

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

Q27. Your company uses a proprietary encryption algorithm to secure sensitive data. A custom cryptographic assembly was developed in Microsoft .NET and is used in several applications. 

A SQL Server Integration Services (SSIS) package is importing data into a Windows Azure SQL Database database. Some of the data must be encrypted with the proprietary encryption algorithm. 

You need to design the implementation strategy to meet the requirements while minimizing development and deployment effort and maximizing data flow performance. 

What should you do? 

A. Create a SQL Common Language Runtime (SQLCLR) function that uses the custom assembly to encrypt the data, deploy it in the Windows Azure SQL Database database, and use it when inserting data. 

B. Use an SSIS Script transformation that uses the custom assembly to encrypt the data when inserting it. 

C. Create a SQL Common Language Runtime (SQLCLR) stored procedure that uses the custom assembly to encrypt the data, deploy it in the Windows Azure SQL Database database, and use it when inserting data. 

D. Use an SSIS Script task that uses the custom assembly to encrypt the data when inserting it. 

Answer: B 

Q28. 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: C 

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

Answer: 

Q30. A SQL Server Integration Services (SSIS) package is designed to download data from a financial database hosted in SQL Azure. 

The connection string to the financial database is defined as a project parameter named FinConStr. The parameter value must be stored securely and must be set explicitly every time the package is executed. 

You need to configure the parameter to meet the requirements. 

What should you do? (To answer, configure the appropriate option or options in the dialog box in the answer area.) 

Answer: