Microsoft 70-463 Free Practice Questions

Q1. You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS).

You need to complete the post-installation configuration.

What should you do?

A. Run the Data Quality Server Installer.

B. Install the data providers that are used for data refresh.

C. Run the dbimpexp.exe command.

D. Install the Analysis Services OLE DB Provider.

Answer: B

Q2. You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. You are developing the part of the SSIS package that populates the ProjectDates dimension table.

The business key of the ProjectDates table is the ProjectName column. The business user has given you the dimensional attribute behavior for each of the four columns in the ProjectDates table:

ExpectedStartDate - New values should be tracked over time.

ActualStartDate - New values should not be accepted.

ExpectedEndDate - New values should replace existing values.

ActualEndDate - New values should be tracked over time.

You use the SSIS Slowly Changing Dimension Transformation.

You must configure the Change Type value for each source column.

Which settings should you select? (To answer, select the appropriate setting or settings in the answer area. Each Change Type may be used once, more than once, or not at all.)

Answer:

Q3. You are developing a SQL Server Integration Services (SSIS) package.

The package contains an ADO object source variable that holds a result set that was returned by a stored procedure execution.

You need to add and configure a container that will execute several tasks for each row in the ADO object source variable.

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:

Q4. 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. Parent Package Variable

B. XML Configuration File

C. Environment Variable

D. .dtsConfig file

E. Registry Entry

Answer: C

Q5. You are designing a SQL Server Integration Services (SSIS) package configuration strategy.

The package configuration must meet the following requirements: Include multiple properties in a configuration. Force packages to load all settings in the configuration. Support Encrypting File System (EFS) formats. You need to select the appropriate configuration.

Which configuration type should you use?

To answer, select the appropriate option from the drop-down list in the dialog box.

Answer:

Q6. You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages.

All packages in the project must connect to the same database. The server name for the database must be set by using a parameter named ServerParam when any package in the project is executed.

You need to develop this project with the least amount of development effort.

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

A. Set the Sensitive property of the parameter to True.

B. Edit each package Connection Manager. Set the ServerName property to @[$Project::ServerParam].

C. Edit the project Connection Manager in Solution Explorer. Set the ServerName property to @[$Project::ServerParam].

D. Create a project parameter named ServerName.

E. Create a package parameter named ServerName in each package.

F. Set the Required property of the parameter to True.

Answer: B,D,F

Explanation: B: From question: " The server name for the database must be set by using a parameter named ServerParam when any package in the project is executed."

D: SSIS 2012 has introduced the concept of Project level connection managers. An SSIS project is generally more than one package. To simplify lives, the SSIS team now allows for the sharing of common resources across projects, connection managers being one of those resources.

F: When a parameter is marked as required, a server value or execution value must be specified for that parameter. Otherwise, the corresponding package does not execute. Although the parameter has a default value at design time, it will never be used once the project is deployed.

Note:

* Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package.

Reference: Integration Services (SSIS) Parameters

Q7. You manage a SQL Server Master Data Services (MDS) environment.

A new application requires access to the product data that is available in the MDS repository.

You need to design a solution that gives the application access to the product data with the least amount of development effort.

What should you do?

A. Use sp_addlinkedserver to add a linked server to access the MDS database tables directly.

B. Create an OLE DB connection string that sets the Provider property to MDS.

C. Use transactional replication for data synchronization.

D. Create a Subscription View in MDS.

Answer: D

Q8. A SQL Server Integration Services (SSIS) package was deployed two weeks ago with the Project Deployment Model.

Sometimes the package is started as part of a multistep SQL job. At other times, the package is started manually by a database administrator by using the Object Explorer in SQL Server Management Studio.

You need to identify the authenticated user responsible for starting the package each time it executes.

How can you find this information?

A. In the SSISDB.[catalog], query the .[executions] view.

B. In the SSISDB.[catalog] , query the [event_messages] view.

C. In SQL Server Management Studio, view the SQL Agent Job History.

D. In SQL Server Management Studio, view the SQL Agent Error Log.

E. In SQL Server Management Studio, view the SQL Server Log.

Answer: A

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

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

Q10. You are building a SQL Server Integration Services (SSIS) package to load data from all files that are automatically copied to a directory each night through an external FTP process.

You need to load data from all copied files to a destination table in SQL Server.

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:

Q11. You are designing a SQL Server Integration Services (SS1S) package that uploads a file to a table named Orders in a SQL Azure database.

The company's auditing policies have the following requirements:

. An entry must be written to a dedicated SQL Server log table named OrderLog.

. The entry must be written as soon as the file upload task completes.

You need to meet the company's policy requirements. Which event handler should you use?

A. OnProgress

B. Onlnformation

C. OnPostExecute

D. OnComplete

Answer: C

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

Q12. 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: E

Q13. You are developing a SQL Server Integration Services (SSIS) package.

The package must run a parameterized query against a Windows Azure SQL Database

database.

You need to use the least amount of development effort to meet the package requirement.

Which task should you use? (To answer, select the appropriate task in the answer area.)

Answer:

Q14. You manage a SQL Server Master Data Services (MDS) environment.

A new application requires access to the product data that is available in the MDS repository.

You need to design a solution that gives the application access to the product data with the least amount of development effort.

What should you do?

A. Create a Subscription View in MDS.

B. Access the product entity tables in the MDS database directly.

C. Use SQL Server Integration Services (SSIS) to extract the data and put it in a staging database.

D. Use change data capture on the product entity tables.

Answer: A

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

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.

Q16. You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse. The package consists of several data flow tasks.

The package experiences intermittent errors in the data flow tasks.

If any data flow task fails, all package error information must be captured and written to a SQL Server table by using an OLE DB connection manager.

You need to ensure that the package error information is captured and written to the table.