Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 221:

  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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You are developing a Microsoft SQL Server Integration Services (SSIS) projects. The project consists of several packages that load data warehouse tables.

  You need to extend the control flow design for each package to use the following control flow while minimizing development efforts and maintenance:

  

  Solution: You add the control flow to an ASP.NET assembly. You add a script task that references this assembly to each data warehouse load package. Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  A package consists of a control flow and, optionally, one or more data flows. You create the control flow in a package by using the Control Flow tab in SSIS Designer. References: https://docs.microsoft.com/en-us/sql/integration-services/control-flow/control- flow

• Question 222:

  You manage a data warehouse in a Microsoft SQL Server instance. Company employee information is imported from the human resources system to a table named Employee in the data warehouse instance. The Employee table was created by running the query shown in the Employee Schema exhibit. (Click the Exhibit button.)

  

  The personal identification number is stored in a column named EmployeeSSN. All values in the EmployeeSSN column must be unique. When importing employee data, you receive the error message shown in the SQL Error exhibit. (Click the Exhibit button.).

  

  You determine that the Transact-SQL statement shown in the Data Load exhibit in the cause of the error. (Click the Exhibit button.)

  You remove the constraint on the EmployeeSSN column. You need to ensure that values in the EmployeeSSN column are unique.

  For each of the following statements, select Yes if the statement is true. Otherwise, select No.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  With the ANSI standards SQL:92, SQL:1999 and SQL:2003, an UNIQUE constraint must disallow duplicate non-NULL values but accept multiple NULL values.

  In the Microsoft world of SQL Server however, a single NULL is allowed but multiple NULLs are not.

  From SQL Server 2008, you can define a unique filtered index based on a predicate that excludes NULLs.

  References: https://stackoverflow.com/questions/767657/how-do-i-create-a-uniqueconstraint-that-also-allows-nulls

• Question 223:

  You have a Microsoft SQL Server Integration Services (SSIS) package that loads data into a data warehouse each night from a transactional system. The package also loads data from a set of Comma-Separated Values (CSV) files that are

  provided by your company's finance department.

  The SSIS package processes each CSV file in a folder. The package reads the file name for the current file into a variable and uses that value to write a log entry to a database table.

  You need to debug the package and determine the value of the variable before each file is processed.

  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:

  

  

  ---

  You debug control flows.

  The Foreach Loop container is used for looping through a group of files. Put the breakpoint on it.

  The Locals window displays information about the local expressions in the current scope of the Transact-SQL debugger.

  References: https://docs.microsoft.com/en-us/sql/integrationservices/troubleshooting/debugging-control-flow

  http://blog.pragmaticworks.com/looping-through-a-result-set-with-the-foreach-loop

• Question 224:

  You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model. All packages in the project must log custom messages.

  You need to produce reports that combine the custom log messages with the system- generated log messages. 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,
  G. Store the System::ServerExecutionID variable in the custom log table.

• Question 225:

  You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS).

  You need to complete the post-installation configuration.

  What should you do?

  A. Run the DQSInstaller.exe command.
  B. Install the data providers that are used for data refresh.
  C. Install ADOMD.NET.
  D. Run the dbimpexp.exe command.
  A. Run the DQSInstaller.exe command.

  ---

  http://msdn.microsoft.com/en-us/library/ff877917.aspx http://msdn.microsoft.com/en-us/library/gg492277.aspx

• Question 226:

  You install a SQL Server 2016 database engine instance on a production server. A month later, you install SQL Server 2016 Integration Services (SSIS).

  You must develop an SSIS project and deploy it to the server by using the Project Deployment model.

  Operations Log records that are outside the configured retention period must be cleaned automatically.

  You need to create the SSIS catalog on the production server.

  What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

  A. Enable XP Command Shell.
  B. Enable CLR Integration.
  C. Enable OLE Automation.
  D. Start the SQL Server Browser service.
  E. Enable Cross Database Ownership Chaining
  F. Start the SQL Server Agent service
  G. Enable Ad Hoc Remote Queries.
  B. Enable CLR Integration.
  F. Start the SQL Server Agent service

  ---

  Explanation: Ref: http://msdn.microsoft.com/en-us/library/gg471509(v=sql.110).aspx

• Question 227:

  You are adding a new capability to several dozen SQL Server Integration Services (SSIS) packages.

  The new capability is not available as an SSIS task. Each package must be extended with the same new capability.

  You need to add the new capability to all the packages without copying the code between packages. What should you do?

  A. Use the Expression task.
  B. Use the Script task.
  C. Develop a custom task.
  D. Use the Script component,
  E. Develop a custom component.
  C. Develop a custom task.

  ---

  http://msdn.microsoft.com/en-us/library/ms135965.aspx http://msdn.microsoft.com/en-us/library/ms345161.aspx

• Question 228:

  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 sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have the following line-of-business solutions:

  If a change is made to the ReferenceNr column in any of the sources, set the value of IsDisabled to True and create a new row in the Products table. If a row is deleted in any of the sources, set the value of IsDisabled to True in the data

  warehouse.

  One or more Microsoft SQL Server instances support each solution. Each solution has its own product catalog. You have an additional server that hosts SQL Server Integration Services (SSIS) and a data warehouse. You populate the data

  warehouse with data from each of the line-of-business solutions. The data warehouse does not store primary key values from the individual source tables.

  The database for each solution has a table named Products that stored product information. The Products table in each database uses a separate and unique key for product records. Each table shares a column named ReferenceNr between

  the databases. This column is used to create queries that involve more than once solution.

  You need to load data from the individual solutions into the data warehouse nightly. The following requirements must be met:

  Enable the Change Tracking for the Product table in the source databases. Query the cdc.fn_cdc_get_all_changes_capture_dbo_products function from the sources for updated rows.

  Set the IsDisabled column to True for rows with the old ReferenceNr value. Create a new row in the data warehouse Products table with the new ReferenceNr value.

  Solution: Perform the following actions:

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  We must also handle the deleted rows, not just the updated rows.

  References: https://solutioncenter.apexsql.com/enable-use-sql-server-change-data- capture/

• Question 229:

  You are designing a SQL Server Integration Services (SS1S) package that uploads a file to a table named Orders in a SQL Azure database.

  The company's auditing policies have the following requirements:

  A. OnProgress
  B. Onlnformation
  C. OnPostExecute
  D. OnComplete

  C. OnPostExecute

  ---

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

• Question 230:

  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 have an on-premises Microsoft SQL Server instance and a Microsoft Azure SQL Data Warehouse instance. You move data from the on-premises database to the data warehouse once each day by using a SQL Server Integration

  Services (SSIS) package.

  You observe that the package no longer completes within the allotted time.

  You need to determine which tasks are taking a long time to complete.

  Solution: You alter the package to log the start and completion times for a task to a table in the on-premises SQL Server instance.

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes