Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 391:

  DRAG DROP

  You are designing a SQL Server Integration Services (SSIS) package to execute 12 Transact-SQL (T-SQL) statements on a SQL Azure database. The T-SQL statements may be executed in any order. The T-SQL statements have

  unpredictable execution times.

  You have the following requirements:

  The package must maximize parallel processing of the T-SQL statements.

  After all the T-SQL statements have completed, a Send Mail task must notify administrators.

  You need to design the SSIS package. 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 392:

  DRAG DROP

  You plan to deploy a SQL Server Integration Services (SSIS) project by using the project deployment model.

  You need to monitor control flow tasks to determine whether any of them are running longer than usual. 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 393:

  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 are the administrator of a Microsoft SQL Server Master Data Services (MDS) instance. The instance contains a model named Geography and a model named customer. The Geography model contains an entity named countryRegion.

  You need to ensure that the countryRegion entity members are available in the customer model.

  Solution: Configure an entity sync relationship to replicate the CountryRegion entity.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 394:

  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 designing a data warehouse and the load process for the data warehouse.

  You have a source system that contains two tables named Table1 and Table2. All the rows in each table have a corresponding row in the other table.

  The primary key for Table1 is named Key1. The primary key for Table2 is named Key2.

  You need to combine both tables into a single table named Table3 in the data warehouse. The solution must ensure that all the nonkey columns in Table1 and Table2 exist in Table3.

  Which component should you use to load the data to the data warehouse?

  A. the Slowly Changing Dimension transformation
  B. the Conditional Split transformation
  C. the Merge transformation
  D. the Data Conversion transformation
  E. an Execute SQL task
  F. the Aggregate transformation
  G. the Lookup transformation
  G. the Lookup transformation

  ---

  The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. You use the lookup to access additional information in a related table that is based on values in common columns.

  You can configure the Lookup transformation in the following ways:

  Specify joins between the input and the reference dataset.

  Add columns from the reference dataset to the Lookup transformation output.

  Etc.

• Question 395:

  DRAG DROP

  You are developing a SQL Server Integration Services (SSIS) package that loads data into a data warehouse hosted on Windows Azure SQL Database.

  You must combine two data sources together by using the ProductID column to provide complete details for each record. The data retrieved from each data source is sorted in ascending order by the ProductID column.

  You need to develop a data flow that imports the data while meeting the requirements.

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

  Select and Place:

  

  

• Question 396:

  You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model.

  The project will be deployed to an SSIS catalog folder where Environments have already been created. You need to deploy the project. 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,
  M

• Question 397:

  A company plans to load data from a CSV file that is stored in a Microsoft Azure Blob storage container.

  You need to load the data.

  How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag

  the split bar between panes or scroll to view content.

  NOTE: Each correct selection is worth one point.

  Select and Place:

  

  

• Question 398:

  You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model.

  The project is deployed to a single SSIS catalog, and transfers data to and from multiple databases hosted on SQL Server.

  The project must be configured to be able to export data to and from five different production servers that run SQL Server 2016. Each target server requires different values for connection strings and parameters in the SSIS project.

  You need to meet the requirements by using the least amount of administrative effort.

  What should you do?

  A. For each target server, create separate registry entry configurations. Select the registry entry at package execution time.
  B. For each target server, create separate Environments in the SSIS catalog of the host SQL Server SSIS instance. Select the appropriate Environment at package execution time.
  C. Create one SSIS catalog Environment. Change the values of each Environment variable at package execution time.
  D. For each target server, create a separate XML configuration file. Select the XML configuration file at package execution time.
  B. For each target server, create separate Environments in the SSIS catalog of the host SQL Server SSIS instance. Select the appropriate Environment at package execution time.

• Question 399:

  DRAG DROP

  You are developing a SQL Server Integration Services (SSIS) package that imports unsorted data into a data warehouse hosted on SQL Azure. You have the following requirements:

  A destination table must contain all of the data in two source tables.

  Duplicate records must be inserted into the destination table.

  You need to develop a data flow that imports the data while meeting the requirements.

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

  Select and Place:

  

  

• Question 400:

  You administer a Microsoft SQL Server 2016 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)

  

  Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you use an identity specification to include a seed of 1,000 and an increment of 1?

  A. DateHired
  B. DepartmentID
  C. EmployeeID
  D. EmployeeNum
  E. FirstName
  F. JobTitle
  G. LastName
  H. MiddleName
  I. ReportsToID
  C. EmployeeID