Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 131:

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

  The package experiences intermittent errors in the data flow tasks.

  If any data flow task fails, all package error information must be captured and written to a SQL Server table by using an OLE DB connection manager.

  You need to ensure that the package error information is captured and written to the table.

  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.
  I. Enable the SSIS log provider for SQL Server for OnError in the package control flow.

• Question 132:

  You are reviewing the design of an existing fact table named factSales, which is loaded incrementally from a SQL Azure database by a SQL Server Integration Services (SSIS) package each hour. The fact table has approximately 4 billion rows and is dimensioned by product, sales date, and sales time of day.

  

  The database administrator is concerned about the rapid growth of the database and users experience poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses

  this fact table reports sales by product name, sale month, and sale year. No other reports will be created against this table.

  You need to reduce the report processing time and minimize the growth of the database.

  What should you do?

  A. Create an indexed view over the fact table to sum orderTotal by month.
  B. Create a view over the fact table to sum orderTotal by month.
  C. Change the granularity of the fact table to month.
  D. Partition the fact table by productKey.
  C. Change the granularity of the fact table to month.

• Question 133:

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

  What should you use?

  A. script component
  B. custom component
  C. XML source
  D. script task
  A. script component

• Question 134:

  You are using a SQL Server Integration Services (SSIS) project that is stored in the SSIS catalog. An Environment has been defined in the SSIS catalog.

  You need to add the Environment to the project.

  Which stored procedure should you use?

  A. catalog.set_environment_reference_type
  B. catalog.set_environment_property
  C. catalog.create_environment_reference
  D. catalog.create_environment
  C. catalog.create_environment_reference

• Question 135:

  You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD).

  You need to add a task or component to the package that allows you to implement the SCD logic.

  What should you use?

  A. a Script component
  B. an SCD component
  C. an Aggregate component
  D. a Merge component
  D. a Merge component

• Question 136:

  You are using a SQL Server Integration Services (SSIS) project that is stored in the SSIS catalog. An Environment has been defined in the SSIS catalog.

  You need to add the Environment to the project.

  Which stored procedure should you use?

  A. catalog.create_environment_variable
  B. catalog.create_environment_reference
  C. catalog.set_execution_parameter_value
  D. catalog.set_environment_variable_value
  B. catalog.create_environment_reference

  ---

  Environments (Test, Production etc) are associated with projects by creating references to the environments in the projects.

• Question 137:

  You deploy a Microsoft Server database that contains a staging table named EmailAddress_Import. Each night, a bulk process will import customer information from an external database, cleanse the data, and then insert it into the

  EmailAddress table.

  Both tables contain a column named EmailAddressValue that stores the email address.

  You need to implement the logic to meet the following requirements:

  Email addresses that are present in the EmailAddress_Import table but not in the EmailAddress table must be inserted into the EmailAddress table.

  Email addresses that are not in the EmailAddress_Import but are present in the EmailAddress table must be deleted from the EmailAddress table.

  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.

  Select and Place:

  

  

  ---

  Box 1: EmailAddress The EmailAddress table is the target. Box 2: EmailAddress_import The EmailAddress_import table is the source. Box 3: NOT MATCHED BY TARGET Box 4: NOT MATCHED BY SOURCE References: https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql

• Question 138:

  You are deploying a new SQL Server Integration Services (SSIS) project to the test environment.

  A package in the project uses a custom task component.

  You need to ensure that the custom object is deployed on the test environment correctly.

  What should you do?

  A. Run the package by using the dtexec /rep /conn command.
  B. Create a reusable custom logging component.
  C. Create an OnError event handler.
  D. Use the gacutil command.
  E. Use the dtutil /copy command.
  F. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
  G. Run the package by using the dtexec /dumperror /conn command.
  H. Use the Project Deployment Wizard.
  I. Deploy the package by using an msi file.
  J. Add a data tap on the output of a component in the package data flow.
  D. Use the gacutil command.

  ---

  Reference: http://msdn.microsoft.com/en-us/library/ms403356.aspx

• Question 139:

  You are reviewing the design of a customer dimension table in an existing data warehouse hosted on SQL Azure.

  The current dimension design does not allow the retention of historical changes to customer attributes such as Postcode.

  You need to redesign the dimension to enable the full historical reporting of changes to multiple customer attributes including Postcode.

  What should you do?

  A. Add StartDate and EndDate columns to the customer dimension.
  B. Add an IsCurrent column to the customer dimension.
  C. Enable Snapshot Isolation on the data warehouse.
  D. Add CurrentValue and PreviousValue columns to the customer dimension.
  A. Add StartDate and EndDate columns to the customer dimension.

• Question 140:

  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: Create a CountryRegion entity in the Customer model. In the Geography model, create a subscription view and load data to the entity-based staging table that contains the CountryRegion entity.

  Does this meet the goal?

  A. Yes
  B. No
  B. No