Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 231:

  DRAG DROP

  You are developing a SQL Server Integration Services (SSIS) package that is ready for deployment to a production server. The package contains sensitive information secured by using the EncryptSensitiveWithUserKey package protection

  level.

  You are preparing the package for deployment by the production operations team.

  You need to ensure that the production operations team can open and execute the package without re-entering the sensitive information.

  Which three steps 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 232:

  You are using SQL Server Data Tools to develop a SQL Server Integration Services (SSIS) project.

  The first package that you create in this project contains a package connection that accesses a flat file. Additional packages in the project must also access this file.

  You need to define and reuse the flat file connection in all project packages.

  What should you do?

  A. Convert the package Connection Manager in the first package to a project Connection Manager.
  B. Copy the package Connection Manager and paste it into the second package.
  C. Convert the project to the Package Deployment model.
  D. Set the ProtectionLevel property of the package Connection Manager to DontSaveSensitive to reuse the flat file connection.
  A. Convert the package Connection Manager in the first package to a project Connection Manager.

• Question 233:

  HOTSPOT

  You are the Master Data Services (MDS) administrator at your company.

  An existing user must be denied access to a certain hierarchy node for an existing model.

  You need to configure the user's permissions.

  Which user management menu item should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

  Hot Area:

  

  

• Question 234:

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

  You create a script task in the package.

  You need to dynamically set the file name of a connection object named File1 by using the script task

  Which line of code should you add to the script task?

  A. Me.Dts.Connections("File1").AcquireConnection("C:\temp\temp.txt")
  B. Me.Dts.Connections("File1").Variables.Item["FileName"].value = "C:\temp\temp.txt"
  C. Me.Dts.Connections("File1").Properties.Item["FileName"].Setvalue = "C:\temp\temp.txt"
  D. Me.Dts.Connections("File1").ConnectionString = "C:\temp\temp.txt"
  C. Me.Dts.Connections("File1").Properties.Item["FileName"].Setvalue = "C:\temp\temp.txt"

• Question 235:

  DRAG DROP

  You are the administrator for a Data Quality Server. You are adding a user who must have permission to:

  Edit and execute a project

  View the activity monitoring data

  This user must not be able to:

  Perform any kind of knowledge management

  Create or change a knowledge base

  Terminate an activity or perform administrative duties

  You need to develop a Transact-SQL (T-SQL) script to meet these requirements.

  What should you do? (To answer, drag the appropriate code segment or segments to the correct location or locations in the answer area.)

  Select and Place:

  

  

• Question 236:

  DRAG DROP

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

  The project contains many packages. It is deployed on a server named SQLTest1. The project will be deployed to several servers that run SQL Server 2016.

  The project accepts one required parameter. The data type of the parameter is a string.

  A SQL Agent job is created that will call the Loading.dtsx package in the project. A job step is created for the SSIS package.

  The job must pass the value of an SSIS Environment Variable to the project parameter.

  The value of the Environment Variable must be configured differently on each server that runs SQL Server. The value of the Environment Variable must provide the server name to the project parameter.

  You need to configure SSIS on the SQLTest1 server to pass the Environment Variable to the package.

  Which four actions should you perform in sequence by using SQL Server Management Studio?

  (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 237:

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

  You need to design a package to change a variable value during package execution by using the least amount of development effort.

  What should you use?

  A. Expression task
  B. Data Cleansing transformation
  C. Fuzzy Lookup transformation
  D. Term Lookup transformation
  E. Data Profiling task
  A. Expression task

• Question 238:

  You have a database that contains a table named Email. Change Data Capture (CDC) is enabled for the table.

  You have a Microsoft SQL Server Integration Services (SSIS) package that contains the Data Flow task shown in the Data Flow exhibitA.

  

  You have an existing CDC source as shown in the CDC Source exhibitB (Missing exhibitB) and a CDC Splitter transform as shown in the CDC Splitter exhibitC.

  You need to perform an incremental import of customer email addresses. Before importing email addresses, you must move all previous email addresses to another table for later use.

  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:

  

  

• Question 239:

  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:

  ERP system

  Online WebStore

  Partner extranet

  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:

  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.

  Solution: Perform the following actions:

  Enable the Change Tracking for the Product table in the source databases. Query the CHANGETABLE function from the sources for the updated rows. Set the IsDisabled column to True for the listed rows that have the old ReferenceNr

  value.

  Create a new row in the data warehouse Products table with the new ReferenceNr value.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  We must check for deleted rows, not just updates rows.

  References: https://www.timmitchell.net/post/2016/01/18/getting-started-with-change- tracking-in-sql-server/

• Question 240:

  You are designing a method to split a partition that already contains data within a Microsoft Azure SQL Data Warehouse. You run the following Transact-SQL statements:

  

  Use the drop-down menus to select the answer choice that answers each question. NOTE: Each correct selection is worth one point.

  Hot Area: