Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 241:

  You have a SQL Server Integration Services (SSIS) package that contains several data flows.

  You discover that several records fail to load correctly in the destination.

  You need to identify the cause of the record failure for each record.

  Which event handler should you add to the package?

  A. OnTaskFailed
  B. OnError
  C. OnPostExecute
  D. OnExecStatusChanged
  B. OnError

  ---

  The OnError event is generated when one or more errors occurred due to a problem within the Package for any reason within the related Data Processing objects. Incorrect Answers:

  A: The OnTaskFailed event is generated only once when a Task "failure", independently of failure occurrence be in the Package context or just in a Task specific. References: https://social.technet.microsoft.com/wiki/contents/articles/24643.ssis-event-handling-with-onerror-or-ontaskfailed.aspx

• Question 242:

  You are developing a SQL Server Integration Services (SSIS) package. The package contains a user-defined variable named Queue which has an initial value of 10.

  The package control flow contains many tasks that must repeat execution until the Queue variable equals 0.

  You need to enable the tasks to be grouped together for repeat execution.

  Which item should you add to the package? (To answer, select the appropriate item in the answer area.)

  Hot Area:

  

  

• Question 243:

  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. Install a 64-bit ACE driver and replace the Excel source with an OLE DB source.
  B. Install a 32-bit instance of SSIS side-by-side with the 64-bit version.
  C. Replace the SSIS Excel source with a SQL Server source.
  D. Create a custom SSIS source component that encapsulates the 32-bit driver and compile it in 64-bit mode.
  A. Install a 64-bit ACE driver and replace the Excel source with an OLE DB source.

  ---

  XLSX files, the new file type in Excel2007/2010 cannot be opened with Excel Source/Destination in SSIS. To use XLSX in SSIS we need to install ACE driver and use OLE DB Source/Destination to read read/write data in XLSX files.

  ACE is a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access (*.mdb and *.accdb) files and Microsoft Office Excel 7 (*.xls, *.xlsx, and *.xlsb) files to Microsoft SQL Server.

  If the SQL Server or your development box does not have ACE driver then install that 1st.

  Note:

  * The most efficient way to link to SQL Server is using an OLEDB connection manager.

  References: https://docs.microsoft.com/en-us/sql/integration-services/connection-manager/connect-to-an-excel-workbook

• Question 244:

  You administer a Microsoft SQL Server database. Service accounts for SQL Agent are configured to use a local user. A Microsoft SQL Server Integration Services (SSIS) job step has been created within a SQL Server Agent job. The SSIS

  package accesses a network share when exporting data from a SQL Server database.

  When you execute the SQL Server Agent job, it fails due to a permissions failure on a share on a remote server. You need to ensure that the SQL Server Agent job can execute the SSIS package.

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

  Your company has a data mart that contains customer review data.

  The customer reviews are of the product review or service review type.

  You plan to load each customer review type to a separate fact table.

  You create a SQL Server Integration Services (SSIS) package as shown in the exhibit. (Click the Exhibit button.)

  

  You need to modify the package to load the customer review data to the new fact tables. The SSIS package must contain a single data flow. What should you do?

  A. Set the transformation to Conditional Split.
  B. Set the transformation to CDC Splitter.
  C. Add a second transformation and set the transformation to Merge.
  D. Add a second transformation and set the transformation to Multicast.
  A. Set the transformation to Conditional Split.

  ---

  References: https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/conditional-split-transformation

• Question 246:

  A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse.

  The SSIS catalog includes two Environments: Development QA Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the development or QA data warehouses.

  You need to be able to execute deployed packages by using either of the defined Environments.

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

  Select and Place:

  

  

• Question 247:

  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 tool that executes scheduled administrative tasks in SQL Server 2012. Which of the following is the tool you are discussing?

  A. Object Explorer
  B. SQL Server Agent
  C. Template Explorer
  D. Solution Explorer
  B. SQL Server Agent

• Question 248:

  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 SQL Azure. 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. Audit
  B. Source Assistant
  C. Script Task
  D. Conditional Split
  D. Conditional Split

  ---

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

  A Script Component (Script Component is the answer to another version of this question) could be used but this is not the same as a Script Task.

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

  You are developing a SQL Server Integration Services (SSIS) package to insert new data into a data mart. The package uses a Lookup transformation to find matches between the source and destination.

  The data flow has the following requirements:

  New rows must be inserted.

  Lookup failures must be written to a flat file.

  In the Lookup transformation, the setting for rows with no matching entries is set to Redirect rows to no match output. You need to configure the package to direct data into the correct destinations.

  How should you design the data flow outputs? (To answer, drag the appropriate transformation from the list of answer options to the correct location in the answer area.)

  Select and Place:

  

  

• Question 250:

  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.
  C. Change the similarity threshold to 0.85.

  ---

  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.