Microsoft 70-458 Online Practice Questions and Exam Preparation

Microsoft 70-458 Online Questions & Answers

• Question 41:

  A SQL Server Integration Services (SSIS) 2012 package currently downloads sales data from a Windows Azure SQL Database database. To improve sales data accuracy, exchange rates must be downloaded daily from a public HTTP web service instead of from a weekly flat file. You need to implement the change to the existing package while minimizing the development effort.

  A. a Script component
  B. a Web Service source
  C. a Web Service task
  D. a Script task
  C. a Web Service task

• Question 42:

  Note: This question is 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 new SQL Server Integration Services (SSIS) package to several servers.

  The package must meet the following requirements:

  

  INET Common Language Runtime (CLR) integration in SQL Server must not be enabled.

  

  The Connection Managers used in the package must be configurable without editing the package.

  

  The deployment procedure must be automated as much as possible.

  You need to set up a deployment strategy that meets the requirements.

  A. Use the gacutil command.
  B. use the dtutll /copy command.
  C. use the Project Deployment Wizard.
  D. create an OnError 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.
  B. use the dtutll /copy command.

• Question 43:

  You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads rows from a source system and compares them to rows in a destination system. New rows will be

  inserted and changed rows will be updated.

  You have used a Lookup transformation and a Conditional Split transformation. The Lookup transformation joins the source and destination table on the business key, and includes all columns from the destination table in the data flow output.

  The Conditional Split transformation inspects the destination columns and directs data flow to either insert new records or update existing records.

  You need to configure the Lookup transformation to ensure that all records flow to the Conditional Split transformation, regardless of whether the rows match an existing row in the destination table.

  Which setting should you select? (To answer, select the appropriate option in the answer area.)

  

  Hot Area:

  

  

• Question 44:

  You are designing a data warehouse that uses SQL Server 2012. You are preparing to update the contents of a fact table that contains a non-clustered columnstore index.

  You need to run an update statement against the table.

  A. Change the recovery model of the database to Bulk-logged.
  B. Pause the columnstore index.
  C. Change the non-clustered columnstore index to be a clustered columnstore index.
  D. Drop the columnstore index.
  D. Drop the columnstore index.

• Question 45:

  You administer a Microsoft SQL Server 2012 database.

  You use an OrderDetail table that has the following definition:

  

  You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the CarrierTrackingNumber column.

  Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 46:

  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.

  A. Change the similarity threshold to 0.40.
  B. Decrease the maximum number of matches per lookup.
  C. Change the similarity threshold to 0.55.
  D. Change the Exhaustive property to True.
  C. Change the similarity threshold to 0.55.

• Question 47:

  You are a database administrator for a Microsoft SQL Server 2012 instance.

  You need to ensure that data can be migrated from a production server to two reporting servers with minimal data latency. You also need to ensure that data on the reporting server is always accessible.

  A. Change Data Capture
  B. Database snapshot
  C. Availability Groups
  D. Log Shipping
  C. Availability Groups

• Question 48:

  You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day.

  Referential integrity must be enforced declaratively.

  You need to design a solution that can join a single time dimension to both fact tables.

  What should you do?

  A. Create a surrogate key for the time dimension.
  B. Join the two fact tables.
  C. Create a view on the sales table.
  D. Create a time dimension that can join to both fact tables at their respective granularity.
  A. Create a surrogate key for the time dimension.

• Question 49:

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

  You use a Data Profiling task to examine the data from a source system.

  You need to establish:

  

  The minimum and maximum dates for the datetime columns in the source data

  

  The minimum, maximum, and average values for numeric columns in the source data

  You need to use the appropriate profile type in the Data Profiling task.

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

  

  Hot Area:

  

  

• Question 50:

  You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment model. A package in the project extracts data from a Windows Azure SQL Database database. The package is deployed to SQL Server.

  The package is not producing the desired results.

  You need to generate the .mdmp and .tmp debug files in order to troubleshoot the issues.

  A. Execute the catalog.create_execution_dump stored procedure with the package execution_id.
  B. Run the DTEXEC utility with the /Reporting V option.
  C. Run the DTEXEC utility with the /Logger option.
  D. Execute the catalog.add_data_tap stored procedure with the package execution_id.
  A. Execute the catalog.create_execution_dump stored procedure with the package execution_id.