Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 31:

  A part of your data consolidation process involves extracting data from Excel workbooks.

  Occasionally, the data contains errors that cannot be corrected automatically.

  How can you handle this problem by using SSIS?

  A. Redirect the failed data flow task to an External Process task, open the problematic Excel file in Excel, and prompt the user to correct the file before continuing the data consolidation process.
  B. Redirect the failed data flow task to a File System task that moves the erroneous file to a dedicated location where an information worker can correct it later.
  C. If the error cannot be corrected automatically, there is no way for SSIS to continue with the automated data consolidation process.
  D. None of the answers above are correct. Due to Excel's strict data validation rules, an Excel file cannot ever contain erroneous data.
  B. Redirect the failed data flow task to a File System task that moves the erroneous file to a dedicated location where an information worker can correct it later.

  ---

  References: http://www.techbrothersit.com/2014/09/ssis-what-is-delay-validation-property.html

• Question 32:

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

  An existing user needs to integrate data from other systems into MDS.

  You need to give the user permissions to integrate data.

  Which user management menu item should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

  Hot Area:

  

  

• Question 33:

  Occasionally a job that executes an existing SQL Server Integration Services (SSIS) package does not complete and nothing is processed.

  You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts.

  What should you do?

  A. Create a reusable custom logging component.
  B. Use the gacutil command.
  C. Use the Project Deployment Wizard.
  D. Run the package by using the dtexec /rep /conn command.
  E. Add a data tap on the output of a component in the package data flow.
  F. Create an OnErrorevent handler.
  G. Use the dtutil /copy command.
  H. Deploy the package by using an msi file.
  I. Run the package by using the dtexec /dumperror /conn command.
  J. Run the package by using the dtexecui.exe utility and the SQL Log provider.
  J. Run the package by using the dtexecui.exe utility and the SQL Log provider.

  ---

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

• Question 34:

  To ease the debugging of packages, you standardize the SQL Server Integration Services (SSIS) package logging methodology.

  The methodology has the following requirements:

  

  Centralized logging in SQL Server

  

  Simple deployment

  

  Availability of log information through reports or T-SQL

  

  Automatic purge of older log entries

  

  Configurable log details

  You need to configure a logging methodology that meets the requirements while minimizing the amount of deployment and development effort.

  What should you do?

  A. Deploy the package by using an msi file.
  B. Use the gacutil command.
  C. Create an OnError event handler.
  D. Create a reusable custom logging component.
  E. Use the dtutil /copy command.
  F. Use the Integration Services Deployment Wizard.
  G. Run the package by using the dtexec /rep /conn command.
  H. Add a data tap on the output of a component in the package data flow.
  I. Run the package by using the dtexec /dumperror /conn command.
  J. Run the package by using the dtexecui.exe utility and the SQL Log provider.
  F. Use the Integration Services Deployment Wizard.

  ---

  References: http://msdn.microsoft.com/en-us/library/ms140246.aspx http://www.element61.be/en/resource/sql-server-integration-services-2012-%E2%80%93-project-deployment-model

• Question 35:

  You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.

  You are running a training exercise for Microsoft SQL Server 2012 junior administrators. You are discussing a SQL Server application that can be used to access, configure, manage, administer, and develop all components of SQL Server.

  Which of the following are you discussing?

  A. The Solution Explorer.
  B. The Object Explorer.
  C. The SQL Server Management Studio.
  D. The Template Explorer
  C. The SQL Server Management Studio.

• Question 36:

  Your company is evaluating the data cleansing capabilities of SQL Server Data Quality Services (DQS).

  A stand-alone server will be used to host DQS, as well as all related services necessary to run this service.

  You need to install the DQS components on the server by using the minimum amount of steps possible. You also need to grant permissions to a login named DataStewards to enable DataStewards to edit and execute DQS projects.

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

  Select and Place:

  

  

• Question 37:

  You are designing a partitioning strategy for a large fact table in a data warehouse. Tens of millions of new records are loaded into the data warehouse weekly, outside of business hours.

  Most queries are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level.

  You need to partition the table to maximize the performance of queries. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

  A. Partition the fact table by month, and compress each partition.
  B. Partition the fact table by week.
  C. Partition the fact table by year.
  D. Partition the fact table by day, and compress each partition.
  D. Partition the fact table by day, and compress each partition.

• Question 38:

  You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS).

  The package includes custom scripts that must be upgraded.

  You need to upgrade the package to SQL Server 2012.

  Which tool should you use?

  A. SSIS Upgrade Wizard in SQL Server 2008 BIDS
  B. SSIS Upgrade Wizard in SQL Server Data Tools
  C. SQL Server DTExecUI utility (dtexecui.exe)
  D. SQL Server dtexec utility (dtexec.exe)
  E. SQL Server Integration Services Deployment Wizard
  B. SSIS Upgrade Wizard in SQL Server Data Tools

  ---

  Use the SSIS Package Upgrade Wizard to upgrade SQL Server 2005 Integration Services (SSIS) packages and SQL Server 2008 Integration Services (SSIS) packages to the package format for the current (2012) release of SQL Server Integration Services.

• Question 39:

  You are designing a SQL Server Integration Services (SSIS) 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. OnPostExecute
  B. OnExecStatusChanged
  C. OnWarning
  D. OnVariableValueChanged
  A. OnPostExecute

  ---

  The event handler for the OnPostExecute event is raised by an executable immediately after it has finished running. Incorrect Answers:

  B: The event handler for the OnExecStatusChanged event is raised by an executable when its execution status changes. References: https://docs.microsoft.com/en-us/sql/integration-services/integration-services-ssis-event-handlers?view=sql-server-2017

• Question 40:

  You are developing a SQL Server Integration Services (SSIS) package. The data source for the data flow task is a table that has been configured as a change data capture (CDC) table. You are using a CDC Source component to obtain the CDC data.

  The CDC Source component has the following requirements:

  

  The output must include metadata columns that indicate which source columns have changed.

  

  The output must return only one change row per source row that is modified in the current CDC processing range.

  You need to configure the CDC Source component.

  Which CDC processing mode should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

  Hot Area: