Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 141:

  After you answer a question in this section, you will NOT be able to return to it As a result, these questions will not appear in the review screen.

  You are configuring a Microsoft SQL server named ow1 for a new data warehouse. The server contains eight drives and eight processor cores. Each drive uses a separate physical disk.

  You need to configure storage for the tempdb database. The solution must minimize the amount of time it takes to process daily ETL jobs.

  Solution: You configure eight files for the tenpdb database. You place the files on a drive that contains the operating system files.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

• Question 142:

  CORRECT TEXT

  You administer a Microsoft SQL Server 2016 database. The database contains a table that has the following definition:

  

  You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard. You need to ensure that the following requirements are met:

  

  The first row of the file contains the first row of data.

  

  Each record is of the same length.

  

  The date follows the U.S. date format.

  

  The file supports international characters.

  What should you do? (To answer, simply select the option or options in the answer area that you would configure.)

  

  Correct Answer. Tick the “Unicode” checkbox. Set the Format option to “Fixed Width” Untick the “Column names in the first data row” checkbox.
  Tick the “Unicode” checkbox. Set the Format option to “Fixed Width” Untick the “Column names in the first data row” checkbox.

  ---

  Each record is of the same length = Fixed Width.

  The file supports international characters = Unicode.

  The date follows the U.S. date format = Locale (already configured).

  The first row of the file contains the first row of data = Uncheck “Column names in the first data row”

• Question 143:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while

  others might not have a correct solution.

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You create a set of Microsoft SQL Server Integration Services (SSIS) packages to support an ETL process.

  You need to deploy the SSIS packages and meet the following requirements:

  1.

  Maximize ease of use for backup processes.

  2.

  Manage security of packages from a single place.

  3.

  Maintain versioning of packages.

  4.

  Implement parameters to assign values to packages.

  5.

  Use stored procedures to run SSIS packages.

  6.

  Use SQL Server Agent to run packages.

  Solution: You use the Package Deployment model. You save the packages to the file system.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

• Question 144:

  You are developing a SQL Server Integration Services (SSIS) package to load data into a Windows Azure SQL Database database. The package consists of several data flow tasks.

  The package has the following auditing requirements:

  If a data flow task fails, a Transact-SQL (T-SQL) script must be executed. The T-SQL script must be executed only once per data flow task that fails, regardless of the nature of the error.

  You need to ensure that auditing is configured to meet these requirements.

  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 the SQL 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.
  C. Use an event handler for OnTaskFailed for the package.

• Question 145:

  You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on ServerA. The package includes a data flow and is executed on ServerB. The destination table has its own identity column.

  The destination data load has the following requirements:

  The identity values from the source table must be used. Default constraints on the destination table must be ignored.

  Batch size must be 100,000 rows.

  You need to add a destination and configure it to meet the requirements.

  Which destination should you use?

  A. OLE DB Destination with Fast Load
  B. SQL Server Destination
  C. ADO NET Destination without Bulk Insert
  D. ADO NET Destination with Bulk Insert
  E. OLE DB Destination without Fast Load
  A. OLE DB Destination with Fast Load

  ---

  Reference: http://msdn.microsoft.com/en-us/library/ms141237.aspx Reference: http://msdn.microsoft.com/en-us/library/ms139821.aspx Reference: http://msdn.microsoft.com/en-us/library/ms141095.aspx

• Question 146:

  HOTSPOT

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

  The package must run a parameterized query against a Windows Azure SQL Database database.

  You need to use the least amount of development effort to meet the package requirement.

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

  Hot Area:

  

  

• Question 147:

  You are developing a SQL Server Integration Services (SSIS) project that copies a large amount of rows from a SQL Azure database. The project uses the Package Deployment Model. This project is deployed to SQL Server on a test server.

  You need to ensure that the project is deployed to the SSIS catalog on the production server.

  What should you do?

  A. Open a command prompt and run the dtexec /dumperror /conn command.
  B. Create a reusable custom logging component and use it in the SSIS project.
  C. Open a command prompt and run the gacutil command.
  D. Add an OnError event handler to the SSIS project.
  E. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
  F. Open a command prompt and run the dtexec /rep /conn command.
  G. Open a command prompt and run the dtutil /copy command.
  H. Use an msi file to deploy the package on the server.
  I. Configure the SSIS solution to use the Project Deployment Model.
  J. Configure the output of a component in the package data flow to use a data tap.
  I. Configure the SSIS solution to use the Project Deployment Model.

  ---

  Explanation: References:

  http://msdn.microsoft.com/en-us/library/hh231102.aspx http://msdn.microsoft.com/en-us/library/hh213290.aspx http://msdn.microsoft.com/en-us/library/hh213373.aspx

• Question 148:

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is

  exactly the same in each question in this series.

  You have a Microsoft SQL Server data warehouse instance that supports several client applications.

  The data warehouse includes the following tables: Dimension.SalesTerritory, Dimension.Customer, Dimension.Date, Fact.Ticket, and Fact.Order. The Dimension.SalesTerritory and Dimension.Customer tables are frequently updated. The

  Fact.Order table is optimized for weekly reporting, but the company wants to change it daily. The Fact.Order table is loaded by using an ETL process. Indexes have been added to the table over time, but the presence of these indexes slows

  data loading.

  All data in the data warehouse is stored on a shared SAN. All tables are in a database named DB1. You have a second database named DB2 that contains copies of production data for a development environment. The data warehouse has

  grown and the cost of storage has increased. Data older than one year is accessed infrequently and is considered historical.

  You have the following requirements:

  Implement table partitioning to improve the manageability of the data warehouse and to avoid the need to repopulate all transactional data each night. Use a partitioning strategy that is as granular as possible.

  Partition the Fact.Order table and retain a total of seven years of data.

  Partition the Fact.Ticket table and retain seven years of data. At the end of each month, the partition structure must apply a sliding window strategy to ensure that a new partition is available for the upcoming month, and that the oldest month of data is archived and removed.

  Optimize data loading for the Dimension.SalesTerritory, Dimension.Customer, and Dimension.Date tables.

  Incrementally load all tables in the database and ensure that all incremental changes are processed.

  Maximize the performance during the data loading process for the Fact.Order partition.

  Ensure that historical data remains online and available for querying.

  Reduce ongoing storage costs while maintaining query performance for current data.

  You are not permitted to make changes to the client applications.

  You need to configure the Fact.Order table.

  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:

  

  

  ---

  From scenario: Partition the Fact.Order table and retain a total of seven years of data.

  Maximize the performance during the data loading process for the Fact.Order partition.

  Step 1: Create a partition function.

  Using CREATE PARTITION FUNCTION is the first step in creating a partitioned table or index.

  Step 2: Create a partition scheme based on the partition function.

  To migrate SQL Server partition definitions to SQL Data Warehouse simply:

  Step 3: Execute an ALTER TABLE command to specify the partition function.

  References: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-datawarehouse-tables-partition

• Question 149:

  You have a Microsoft SQL Server Integration Services (SSIS) package that includes the control flow shown in the following diagram.

  

  You need to choose the enumerator for the Foreach Loop container. Which enumerator should you use?

  A. Foreach SMO Enumerator
  B. Foreach Azure Blob Enumerator
  C. Foreach NodeList Enumerator
  D. Foreach ADO Enumerator
  D. Foreach ADO Enumerator

  ---

  Use the Foreach ADO enumerator to enumerate rows in tables. For example, you can get the rows in an ADO recordset.

• Question 150:

  You have a data quality project that focuses on the Products catalog for the company. The data includes a product reference number.

  The product reference should use the following format: Two letters followed by an asterisk and then four or five numbers. An example of a valid number is XX*55522. Any reference number that does not conform to the format must be rejected

  during the data cleansing.

  You need to add a Data Quality Services (DQS) domain rule in the Products domain.

  Which rule should you use?

  A. value matches pattern ZA*9876[5]
  B. value matches pattern AZ[*]1234[5]
  C. value matches regular expression AZ[*]1234[5]
  D. value matches pattern [a-zA-Z][a-zA-Z]*[0-9][0-9] [0-9][0-9] [0-9]?
  A. value matches pattern ZA*9876[5]

  ---

  For a pattern matching rule: Any letter (A...Z) can be used as a pattern for any letter; case insensitive Any digit (0...9) can be used as a pattern for any digit Any special character, except a letter or a digit, can be used as a pattern for itself Brackets, [], define optional matching

  Example: ABC:0000 This rule implies that the data will contain three parts: any three letters followed by a colon (:), which is again followed by any four digits.