Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 221:

  You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Microsoft Azure SQL Database. One of the columns in the data source is named

  ProductCode.

  Some of the data to be loaded will reference products that need special processing logic in the data flow.

  You need to enable separate processing streams for a subset of rows based on the source product code.

  Which data flow transformation should you use?

  A. Destination Assistant
  B. Source Assistant
  C. Data Conversion
  D. Conditional Split
  D. Conditional Split

  ---

  We use Conditional Split to split the source data into separate processing streams.

  References: http://msdn.microsoft.com/en-us/library/ms137640.aspx http://msdn.microsoft.com/en-us/library/ms141150.aspx http://msdn.microsoft.com/en-us/library/ff929138.aspx http://msdn.microsoft.com/en-us/library/ff929116.aspx

• Question 222:

  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 Change Type values should you select? (To answer, drag the appropriate value from the list of values to the correct location or locations in the answer area.)

  Select and Place:

  

  

• Question 223:

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

  The package is stored as the myPackage.dtsx file in the root directory of the C drive of the SSIS server.

  You need to run the package from the command prompt.

  Which command should you use?

  A. dtexec /sql myPackage
  B. dtexec /d "C:\File System\myPackage.dtsx"
  C. dtexec /f "C:\myPackage.dtsx"
  D. dtexec /com "myPackage.dtsx"
  C. dtexec /f "C:\myPackage.dtsx"

• Question 224:

  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. Processor\% Idle Time
  B. Processor\% User Time
  C. Memory\Pages/sec
  D. SQLServer:SSIS Pipeline 10.0:Buffers in use
  B. Processor\% User Time

  ---

  The Processor: % User Time value "Corresponds to the percentage of time that the processor spends on executing user processes such as SQL Server."

  In other words, this is the percentage of processor non-idle time spent on user processes

  If the Processor:% User Time high values are caused by SQL Server, the reason can be non-optimal SQL Server configuration, excessive querying, schema issues, etc. Further troubleshooting requires finding the process or batch that uses

  so much processor time

  References: https://www.sqlshack.com/sql-server-processor-performance-metrics-part-2-processor-user-time-processor-privilege-time-total-times-thread-metrics/

• Question 225:

  Note: This question is a part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series.

  Information and details provided in a question apply only to that question.

  You are deploying a project to the SQL Server Integration Services (SSIS) catalog.

  You need to ensure that the project is deployed to the production environment with the least amount of administrative effort.

  What should you do?

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

• Question 226:

  You are the administrator for a Data Quality Server. You are adding a user who must have permission to:

  Edit and execute a project

  View the activity monitoring data

  This user must not be able to:

  Perform any kind of knowledge management

  Create or change a knowledge base

  Terminate an activity or perform administrative duties

  You need to develop a Transact-SQL (T-SQL) script to meet these requirements.

  What should you do? (To answer, drag the appropriate code segment or segments to the correct location or locations in the answer area.)

  Select and Place:

  

  

• Question 227:

  You are designing a data warehouse that uses SQL Server.

  The data warehouse contains a table named factSales that stores product sales. The table has a clustered index on the primary key, four foreign keys to dimension tables, and an aggregate column for sales totals. All key columns use the INT

  data type, and the aggregate column uses the MONEY data type.

  You need to increase the speed of data retrieval from the factSales table.

  Which index type should you add to the table?

  A. Full text
  B. Spatial
  C. Non-clustered
  D. Clustered
  E. semantic search
  F. XML
  C. Non-clustered

• Question 228:

  You are using SQL Server Data Tools to develop a SQL Server Integration Services (SSIS) project.

  The first package that you create in this project contains a package connection that accesses a flat file. Additional packages in the project must also access this file.

  You need to define and reuse the flat file connection in all project packages.

  What should you do?

  A. Convert the package Connection Manager in the first package to a project Connection Manager.
  B. Copy the package Connection Manager and paste it into the second package.
  C. Convert the project to the Package Deployment model.
  D. Set the Protection Level property of the package Connection Manager to DontSaveSensitive to reuse the flat file connection.
  A. Convert the package Connection Manager in the first package to a project Connection Manager.

• Question 229:

  To support the implementation of new reports, Active Directory data will be downloaded to a SQL Server database by using a SQL Server Integration Services (SSIS) 2012 package.

  The following requirements must be met:

  

  All the user information for a given Active Directory group must be downloaded to a SQL Server table.

  

  The download process must traverse the Active Directory hierarchy recursively.

  You need to configure the package to meet the requirements by using the least development effort. Which item should you use?

  A. Script task
  B. Script component configured as a transformation
  C. Script component configured as a source
  D. Script component configured as a destination
  C. Script component configured as a source

  ---

  Creating a Source with the Script Component

  You use a source component in the data flow of an Integration Services package to load data from a data source to pass on to downstream transformations and destinations.

  References: https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-scripting-data-flow-script-component-types/creating-a-source-with-the-script-component?view=sql-server-2017

• Question 230:

  You have a data warehouse that is hosted in a SQL Server instance. The data in the data warehouse is loaded by running bcp.exe You discover that the transaction logs regularly fill up the local hard disk on the server and that a WR1TELOG

  wait is present. You need to reduce the amount of disk space used to store the transaction logs and to remove the WR17TLOG wait.

  Which two database property settings should you modify? To answer,select the appropriate settings in the answer area.

  Hot Area: