Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 161:

  You are deploying a project to the SQL Server Integration Services (SSIS) catalog.

  You need to ensure that the project is deployed to the production environment with the least amount of administrative effort.

  What should you do?

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

  ---

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

• Question 162:

  HOTSPOT

  You are designing a SQL Server Integration Services (SSIS) package configuration strategy. The package configuration must meet the following requirements:

  Include multiple properties in a configuration.

  Force packages to load all settings in the configuration.

  Support Encrypting File System (EFS) formats.

  You need to select the appropriate configuration.

  Which configuration type should you use? To answer, select the appropriate option from the drop-down list in the dialog box.

  Hot Area:

  

  

• Question 163:

  You are implementing a SQL Server Integration Services (SSIS) 2016 package that loads data from various flat files and a Windows Azure SQL Database database.

  Daily transactions must be loaded into a staging database. All the SSIS tasks will use the CurrentDate variable as the transaction date.

  You need to set the CurrentDate variable to the date stored in a control table of the Windows Azure SQL Database database when the package starts. You need to achieve this goal by using the least amount of development effort.

  What should you use to set the variable?

  A. an Expression task
  B. an Execute SQL task
  C. a Script component
  D. a Script task
  B. an Execute SQL task

• Question 164:

  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:

  Maximize ease of use for backup processes.

  Manage security of packages from a single place.

  Maintain versioning pf packages.

  Implement parameters to assign values to packages.

  Use stored procedures to run SSIS packages.

  Use SQL Server Agent to run packages.

  Solution: Solution: You use the Project Deployment model. You save the packages to the msdb database.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

• Question 165:

  You are designing a data warehouse that uses SQL Server 2016.

  The data warehouse contains a table named factSales that stores product sales. The table has a clustered index on the primary key, four foreign keys to dimension tables, and an aggregate column for sales totals. All key columns use the INT

  data type, and the aggregate column uses the MONEY data type.

  You need to increase the speed of data retrieval from the factSales table.

  Which index type should you add to the table?

  A. Full text
  B. Spatial
  C. Non-clustered
  D. Clustered
  C. Non-clustered

• Question 166:

  You are developing a SQL Server Integration Services (SSIS) package that imports data from a relational database to a data warehouse.

  You are importing data from a relational table named Projects. The table has change data capture enabled on all columns.

  You need to process only the most recent values from rows that have been inserted or updated since the previous execution of the package.

  Which query should you use as the data source?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  D. Option D

  ---

  Reference: http://msdn.microsoft.com/en-us/library/bb522511.aspx Reference: http://msdn.microsoft.com/en-us/library/bb510627.aspx Reference: http://msdn.microsoft.com/en-us/library/cc645937.aspx

• Question 167:

  You have a data warehouse that includes three databases named DB1, DB2, and DB3 respectively.

  The data warehouse no longer meets the needs of users.

  You plan to modify the design of the data warehouse. The dimensions in the databases have the following requirements:

  DB1 must support overwriting history.

  DB2 must support preserving all history.

  DB3 must support preserving a version of history.

  You need to design the slowly changing dimensions (SCDs) for the databases.

  For each database, which SCD type should you use? To answer, select the appropriate SCD types in the answer area.

  Hot Area:

  

  

• Question 168:

  You develop and deploy a SQL Server Integration Services (SSIS) package.

  The package is stored in the file system.

  You need to execute the package without importing it to the SSIS server.

  What should you use to execute the package? (Each correct answer presents a complete solution. Choose all that apply.)

  A. catalog.start_package
  B. dtexec
  C. SQL Server Management Studio
  D. SQL Server Agent
  B. dtexec
  D. SQL Server Agent

  ---

  Ref: http://technet.microsoft.com/en-us/library/ms141708(v=sql.110).aspx

• Question 169:

  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 have a database named DB1 that has change data capture enabled.

  A Microsoft SQL Server Integration Services (SSIS) job runs once weekly. The job loads changes from DB1 to a data warehouse by querying the change data capture tables.

  You remove the Integration Services job.

  You need to stop tracking changes to the database. The solution must remove all the change data capture configurations from DB1.

  Which stored procedure should you execute?

  A. catalog.deploy_project
  B. catalog.restore_project
  C. catalog.stop.operation
  D. sys.sp.cdc.addjob
  E. sys.sp.cdc.changejob
  F. sys.sp_cdc_disable_db
  G. sys.sp_cdc_enable_db
  H. sys.sp_cdc.stopJob
  F. sys.sp_cdc_disable_db

  ---

  sys.sp_cdc_disable_db disables change data capture for all tables in the database currently enabled. All system objects related to change data capture, such as change tables, jobs, stored procedures and functions, are dropped. References: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-disable-db-transact-sql? view=sql-server-2017

• Question 170:

  You develop a SQL Server Integration Services (SSIS) package that imports SQL Azure data into a data warehouse every night.

  The SQL Azure data contains many misspellings and variations of abbreviations. To import the data, a developer used the Fuzzy Lookup transformation to choose the closest- matching string from a reference table of allowed values. The

  number of rows in the reference table is very large.

  If no acceptable match is found, the Fuzzy Lookup transformation passes a null value.

  The current setting for the Fuzzy Lookup similarity threshold is 0.50.

  Many values are incorrectly matched.

  You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading performance.

  What should you do?

  A. Decrease the maximum number of matches per lookup.
  B. Change the similarity threshold to 0.55.
  C. Change the Exhaustive property to True.
  D. Increase the maximum number of matches per lookup.
  B. Change the similarity threshold to 0.55.

  ---

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