Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 281:

  In your SSIS solution, you need to load a large set of rows into the database as quickly as possible.

  The rows are stored in a delimited text file, and only one source column needs its data type converted from String (used by the source column) to Decimal (used by the destination column).

  What control flow task would be most suitable for this operation?

  A. The File System task would be perfect in this case, because it can read data from files and can be configured to handle data type conversions.
  B. The Bulk Insert task would be the most appropriate, because it is the quickest and can handle data type conversions.
  C. The data flow task would have to be used, because the data needs to be transformed before it can be loaded into the table.
  D. No single control flow task can be used for this operation, because the data needs to be extracted from the source file, transformed, and then loaded into the destination table. At least three different tasks would have to be used--the Bulk Insert task to load the data into a staging database, a Data Conversion task to convert the data appropriately, and finally, an Execute SQL task to merge the transformed data with existing destination data.
  C. The data flow task would have to be used, because the data needs to be transformed before it can be loaded into the table.

  ---

  References: https://docs.microsoft.com/en-us/sql/integration-services/control-flow/data-flow-task

• Question 282:

  You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure.

  Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart.

  You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented.

  What should you do?

  A. Create a junk dimension for the geography dimension.
  B. Implement change data capture.
  C. Create a conformed dimension for the geography dimension.
  D. Create three geography dimensions.
  C. Create a conformed dimension for the geography dimension.

• Question 283:

  To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a logging methodology is put in place.

  The methodology has the following requirements:

  

  The deployment process must be simplified.

  

  All the logs must be centralized in SQL Server.

  

  Log data must be available via reports or T-SQL.

  

  Log archival must be automated.

  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. Open a command prompt and run the gacutil command.
  B. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
  C. Add an OnError event handler to the SSIS project.
  D. Use an msi file to deploy the package on the server.
  E. Configure the output of a component in the package dataflow to use a data tap.
  F. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
  G. Open a command prompt and run the dtexec /rep /conn command.
  H. Open a command prompt and run the dtutil /copy command.
  I. Open a command prompt and run the dtexec /dumperror /conn command.
  J. Configure the SSIS solution to use the Project Deployment Model.
  J. Configure the SSIS solution to use the Project Deployment Model.

  ---

  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 284:

  You have a server named SQL1 that has SQL Server Integration Services (SSIS) installed. SQL1 has eight CPUs, 8 GB of RAM, RAID-10 storage, and a 10-gigabit Ethernet connection.

  A package named Package1 runs on SQL1. Package1 contains 10 data tasks. Package1 queries 3 GB of data from the source system.

  You review the performance statistics on SQL1 and discover that Package1 executes slower than expected.

  You need to identify the problem that is causing Package1 to execute slowly.

  Which performance monitor counter data should you review?

  A. SQLServer:SSIS Pipeline 10.0: Buffers in use
  B. SQLServer: SSIS Pipeline 10.0:Private buffer memory
  C. Memory\Pages/sec
  D. SQLServer:SSIS Pipeline 10.0: Flat butter memory
  B. SQLServer: SSIS Pipeline 10.0:Private buffer memory

  ---

  Private buffer performance counters Instead of being created by an SSIS instance, private buffers are created by particular transformation process themselves. Their purpose is to support and optimize the performance of temporary tasks in Pipeline, like the Aggregation transformation, Sort transformation etc.

  Private buffer memory: this performance counter shows the full amount of physical or virtual memory used by all of the current private buffers within the Data Flow engine task in the monitoring cycle. During a data transformation, if the Data flow engine creates a buffer to support itself (not the transformation), that buffer is not considered as private.

  References: https://www.sqlshack.com/ssis-pipeline-performance-counters-guide/

• Question 285:

  You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart.

  You have the following requirements:

  

  Ensure that the process supports the creation of an exception report that details possible duplicate key values, null ratios within columns, and column-length distributions of values.

  

  Ensure that users can generate the exception report in an XML format.

  

  Use the minimum development effort.

  You need to design the SSIS solution to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

  A. Use a Data Profiling task. Use a Data Flow task to extract the XML output of the Data Profiling task into a SQL Server table. Query the table to view the exceptions.
  B. Use Data Flow tasks to process the clean data.
  C. Use a Data Profiling task. Read the exceptions in Data Profile Viewer.
  D. Design a stored procedure that examines data for common dirty data patterns. Use an Execute SQL task.
  C. Use a Data Profiling task. Read the exceptions in Data Profile Viewer.

• Question 286:

  You are developing a data flow to load sales data into a fact table. In the data flow, you configure a Lookup Transformation in full cache mode to look up the product data for the sale.

  The lookup source for the product data is contained in two tables.

  You need to set the data source for the lookup to be a query that combines the two tables.

  Which page of the Lookup Transformation Editor should you select to configure the query?

  To answer, select the appropriate page in the answer area.

  Hot Area:

  

  

• Question 287:

  You are designing a data warehouse hosted on Windows Azure SQL Database. The data warehouse currently includes the dimUser and dimRegion dimension tables and the factSales fact table. The dimUser table contains records for each

  user permitted to run reports against the warehouse, and the dimRegion table contains information about sales regions.

  The system is accessed by users from certain regions, as well as by area supervisors and users from the corporate headquarters.

  You need to design a table structure to ensure that certain users can see sales data for only certain regions. Some users must be permitted to see sales data from multiple regions.

  What should you do?

  A. For each region, create a view of the factSales table that includes a WHERE clause for the region.
  B. Create a userRegion table that contains primary key columns from the dimUser and dimRegion tables.
  C. Add a region column to the dimUser table.
  D. Partition the factSales table on the region column.
  B. Create a userRegion table that contains primary key columns from the dimUser and dimRegion tables.

• Question 288:

  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.

  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 theS QL 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.
  J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.

  ---

  References: https://docs.microsoft.com/en-us/sql/integration-services/performance/integration-services-ssis-logging

• Question 289:

  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. In each package, create a package parameter named ServerParam.
  B. Edit each package Connection Manager. Set the ServerName property to @[$Project::ServerParam].
  C. Edit each package Connection Manager. Set the ServerName property to @[$Project::ServerParam].
  D. Set the Required property of the parameter to True.
  E. Set the Sensitive property of the parameter to True.
  F. Create a project parameter named ServerParam.
  C. Edit each package Connection Manager. Set the ServerName property to @[$Project::ServerParam].
  D. Set the Required property of the parameter to True.
  F. Create a project parameter named ServerParam.

  ---

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

  F: We use a project parameter.

  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.

  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.

  Incorrect Answers:

  A: We should use a Project parameter, instead of package parameters, to lessen administrative effort.

  References: https://docs.microsoft.com/en-us/sql/integration-services/integration-services-ssis-package-and-project-parameters?view=sql-server-2017

• Question 290:

  You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel workbook data into a Microsoft Azure SQL Database database. The package has been deployed to a 64-bit production SQL Server.

  The package fails when executed on the production server.

  You need to ensure that the package can load the 32-bit Excel workbook data without errors.

  What should you do?

  A. Replace the SSIS Excel source with a SQL Server source.
  B. Enable Address Windowing Extensions (AWE) for the local SQL Server instance.
  C. Install a 32-bit instance of SSIS side-by-side with the 64-bit version.
  D. Execute the package by using the 32-bit run-time option.
  D. Execute the package by using the 32-bit run-time option.

  ---

  If the computer has a 32-bit version of Office, then you have to install the 32-bit version of the components, and you also have to ensure that you run the package in 32-bit mode. References: https://docs.microsoft.com/en-us/sql/integration-services/connection-manager/connect-to-an-excel-workbook