Microsoft 70-463 Exam Practice Test Instant Access

Question 1

You have a SQL Server Integration Services (SSIS) package that is configured as shown in the exhibit. (Click the Exhibit button.)

You need to recommend changes to the package to reduce load times.

Which two changes should you recommend? Each correct answer presents part of the solution.

ARemove the OLE DB command and use a Script task.

BAdd an Execute SQL task that batch-updates changed rows.

CSet the ValidateExternalMetadata property to False.

DAdd an Execute SQL task that truncates and reloads the table.

ERemove the OLE DB command and use an OLE DB destination.

Answer : B, E

If the OLE DB destination uses a fast-load data access mode, you can specify the following fast load options in the user interface, OLE DB Destination Editor, for the destination:

References:

https://docs.microsoft.com/en-us/sql/integration-services/data-flow/ole-db-destination?view=sql-server-2017#fast-load-options

Question 2

You are developing a SQL Server Integration Services (SSIS) package that imports data from a relational database to a data warehouse.

You are importing data from a relational table named Projects. The table has change data capture enabled on all columns.

You need to process only the most recent values from rows that have been inserted or updated since the previous execution of the package.

Which query should you use as the data source?

AOption A

BOption B

COption C

DOption D

Answer : D

Question 3

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?

AParent Package Variable

BXML Configuration File

CEnvironment Variable

D.dtsConfig file

ERegistry Entry

Answer : C

Question 4

You are the administrator of a server that hosts Data Quality Server for a large retail company.

The server had a hardware failure during business hours.

You need to restore the server that hosts Data Quality Server to another server. You have a recent backup of all the required databases.

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

ARestore the DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA databases to another server as soon as possible.

BExecute the DQS_MAIN.internal_core.RestoreDQDatabases stored procedure with the appropriate parameter.

CRestore only the DQS_MAIN and DQS_STAGING_DATA databases to another server as soon as possible.

DExecute the DQS_MAIN.internal_core.InitServer stored procedure with the appropriate parameter.

Answer : A, B

Steps to restore DQS Databases:

Restore DQS_MAIN database.

Restore the DQS_PROJECTS database.

Restore the DQS_STAGING_DATA database.

In Object Explorer, right-click the server, and then clickNew Query.

In the Query Editor window, copy the following SQL statements, and replace<PASSWORD>with the password that you provided during the DQS installation for the database master key:

USE [DQS_MAIN]

EXECUTE [internal_core].[RestoreDQDatabases] '<PASSWORD>'

Press F5 to execute the statements. Check theResultspane to verify that the statements have executed successfully.

Note:

* Backup and restore of SQL Server databases are common operations that database administrators perform for preventing loss of data in a case of disaster by recovering data from the backup databases. Data Quality Server is primarily implemented by two SQL Server databases: DQS_MAIN and DQS_PROJECTS. The backup and restore procedures of the Data Quality Services (DQS) databases are similar to any other SQL Server databases.

Ref: http://msdn.microsoft.com/en-gb/library/hh213068(v=sql.110).aspx

Question 5

You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy.

The package reads data from a source system. Depending on the value in a source column, the package redirects rows to one of five different data flow paths.

You need to add a data flow transformation to support the package redirection.

Which data flow transformation should you use?

AConditional Split

BPivot

CMulticast

DLookup

Answer : A

Question 6

You are preparing to install SQL Server 2012 Master Data Services (MDS).

You need to ensure that the database requirements are met.

What should you install?

AMicrosoft SharePoint Server 2010 Standard Edition SP1

BMicrosoft SharePoint Server 2010 Enterprise Edition SP1

CSQL Server 2012 Data Center (64-bit) x64 on the database server

DSQL Server 2012 Enterprise (64-bit) x64 on the database server

Answer : D

* Master Data Services is a new feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2012.

* SQL Server 2012 Enterprise features include Master Data Services:

Note:

* Microsoft SQL Server Master Data Services is a Master Data Management (MDM) product from Microsoft, which will ship as a part of the Microsoft SQL Server database.Originally code-named Bulldog, Master Data Services is the rebranding of the Stratature MDM product titled +EDM, which Microsoft acquired in June 2007. Master Data Services is architecturally similar to +EDM, with increased integration with other Microsoft applications as well as some new features. Master Data Services first shipped with Microsoft SQL Server 2008 R2.

Question 7

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?

AChange the TransactionOption property of the package to Required.

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

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

DMove the three Execute SQL product tasks into a Sequence container.

Answer : A

References:

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

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

Microsoft Implementing a Data Warehouse with Microsoft SQL Server 2012/2014 70-463 Exam Practice Test