Microsoft 70-767 Online Practice Questions and Exam Preparation

Microsoft 70-767 Online Questions & Answers

• Question 151:

  CORRECT TEXT

  You are designing a package control flow. The package moves sales order data from a SQL Azure transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being added to the transactional database. The current design of the package control flow is shown in the answer area. (Click the Exhibit button.)

  

  The Insert New Orders Data Flow task must meet the following requirements:

  Usage of the tempdb database should not be impacted.

  Concurrency should be maximized, while only reading committed transactions.

  If the task fails, only that task needs to be rolled back.

  You need to configure the Insert New Orders Data Flow task to meet the requirements.

  How should you configure the transaction properties? (To answer, select the appropriate setting or settings in the answer area.)

  

  Correct Answer. IsolationLevel = ReadCommited. TransactionOption = Required
  IsolationLevel = ReadCommited. TransactionOption = Required

  ---

  http://msdn.microsoft.com/en-us/library/ms137690.aspx http://msdn.microsoft.com/en-us/library/ms137749.aspx http://msdn.microsoft.com/enus/library/microsoft.sqlserver.dts.runtime.dtscontainer.isolationlevel.aspx http://msdn.microsoft.com/en-us/library/ms173763.aspx

• Question 152:

  You are developing a Microsoft SQL Server Integration Services (SSIS) package. You create a data flow that has the following characteristics:

  The package moves data from the table [source].Tabid to DW.Tablel.

  All rows from [source].Table1 must be captured in DW.Tablel for error.Tablel.

  The table error.Tablel must accept rows that fail upon insertion into DW.Tablel due to violation of nullability or data type errors such as an invalid date, or invalid characters in a number.

  The behavior for the Error Output on the";OLE DB Destinatio"; object is Redirect. ?The data types for all columns in [sourceJ.Tablel are VARCHAR. Null values are allowed.

  The Data access mode for both OLE DB destinations is set to Table or view - fast load.

  

  

  Use the drop-down menus to select the answer choice that answers each question.

  Hot Area:

  

  

• Question 153:

  Your company uses a proprietary encryption algorithm to secure sensitive data. A custom cryptographic assembly was developed in Microsoft .NET and is used in several applications.

  A SQL Server Integration Services (SSIS) package is importing data into a Windows Azure SQL Database database. Some of the data must be encrypted with the proprietary encryption algorithm.

  You need to design the implementation strategy to meet the requirements while minimizing development and deployment effort and maximizing data flow performance.

  What should you do?

  A. Create a SQL Common Language Runtime (SQLCLR) function that uses the custom assembly to encrypt the data, deploy it in the Windows Azure SQL Database database, and use it when inserting data.
  B. Use an SSIS Script transformation that uses the custom assembly to encrypt the data when inserting it.
  C. Create a SQL Common Language Runtime (SQLCLR) stored procedure that uses the custom assembly to encrypt the data, deploy it in the Windows Azure SQL Database database, and use it when inserting data.
  D. Use an SSIS Script task that uses the custom assembly to encrypt the data when inserting it.
  B. Use an SSIS Script transformation that uses the custom assembly to encrypt the data when inserting it.

• Question 154:

  You are developing a Master Data Management (MDM) solution for a company by using Microsoft SQL Server Integration Services (SSIS), SQL Server Master Data Services (MDS), and SQL Server Data Quality Services (DQS). You have an MDS model named Geography that contains the entities described in the following table.

  

  You define a domain-based attribute in the State entity that references the CountryRegion entity. You define another domain-based attribute in the city entity that references the State and CountryRegion entities. A single derived hierarchy

  named Geography supports navigation between the CountryRegion, State, and City levels.

  Subscription views exist for all entities. The subscription views have the same name as the entity on which they are based.

  You initialize each entity member. New City entity members are imported daily based on customer city values in a Customer Relationship Management (CRM) database. The CRM database is a SQL Server relational database. When new

  cities are imported from the CRM database, the state codes must be standardized to those already defined in the State entity.

  In the CRM database, sales managers and assigned to countries/regions. A sales manager may be assigned to one or more countries/regions. A country/region may have one or more assigned sales managers. The CRM database contains

  a table named ManagerCountryRegion that stores a row for each manager-country/region relationship.

  You create the following MDS users and map each user to an Active Directory Domain Services (AD DS) user account: User1, User2, and User3. Both User1 and User2 belong to the Explorer functional area.

  Users must be able to complete the tasks described in the following table.

  

  You need to complete the development of the SSIS data flow to load cities into the City staging table.

  

  Which transformation type should you use for Component 3?

  A. Import Column
  B. CDC Splitter
  C. Conditional Split
  D. Slowly Changing Dimension (SCD)
  E. Lookup
  A. Import Column

• Question 155:

  You are designing an extract, transform, load (ETL) process for loading data from a SQL Server database into a large fact table in a data warehouse each day with the prior day's sales data.

  The ETL process for the fact table must meet the following requirements:

  

  Load new data in the shortest possible time.

  

  Remove data that is more than 36 months old.

  

  Ensure that data loads correctly.

  

  Minimize record locking.

  

  Minimize impact on the transaction log.

  You need to design an ETL process that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

  A. Partition the destination fact table by date. Insert new data directly into the fact table and delete old data directly from the fact table.
  B. Partition the destination fact table by date. Use partition switching and staging tables both to remove old data and to load new data.
  C. Partition the destination fact table by customer. Use partition switching both to remove old data and to load new data into each partition.
  D. Partition the destination fact table by date. Use partition switching and a staging table to remove old data. Insert new data directly into the fact table.
  B. Partition the destination fact table by date. Use partition switching and staging tables both to remove old data and to load new data.

• Question 156:

  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 captule tables.

  You remove the Integration Services job.

  You need to stop tracking changes to the database temporarily. The solution must ensure that tracking changes can be restored quickly in a few weeks.

  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
  C. catalog.stop.operation

  ---

  catalog.stop_operation stops a validation or instance of execution in the Integration Services catalog.

  References: https://docs.microsoft.com/en-us/sql/integration-services/system-stored-procedures/catalog-stop-operation-ssisdb-database?view=sql-server-2017

• Question 157:

  You are writing a SQL Server Integration Services (SSIS) package that transfers data from a legacy system.

  Data integrity in the legacy system is very poor. Invalid rows are discarded by the package but must be logged to a CSV file for auditing purposes.

  You need to establish the best technique to log these invalid rows while minimizing the amount of development effort.

  What should you do?

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

  ---

  Reference: http://www.rafael-salas.com/2016/01/ssis-2016-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/hh230989.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx

• Question 158:

  You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel workbook data into a Windows Azure SQL Database database. The package has been deployed to a production server that runs Windows Server 2008 R2 and SQL Server 2016.

  The package fails when executed on the production server.

  You need to ensure that the package can load the Excel workbook data without errors. You need to use the least amount of administrative effort to achieve this goal.

  What should you do?

  A. Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option.
  B. Enable Address Windowing Extensions (AWE) for the local SQL Server instance.
  C. Replace the SSIS Excel source with a SSIS Flat File source.
  D. Install a 64-bit ACE driver and replace the Excel source with an OLE DB source.
  D. Install a 64-bit ACE driver and replace the Excel source with an OLE DB source.

  ---

  Explanation: XLSX files, the new file type in Excel 2007/2010 cannot be opened with Excel Source/Destination in SSIS. To use XLSX in SSIS we need to install ACE driver and use OLE DB Source/Destination to read read/write data in XLSX

  files.

  ACE is a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access (*.mdb and *.accdb) files and Microsoft Office Excel 7 (*.xls, *.xlsx, and *.xlsb) files to Microsoft SQL Server.

  If the SQL Server or your development box does not have ACE driver then install that 1st.

  Note:

  *

  The most efficient way to link to SQL Server is using an OLEDB connection manager.

  Incorrect: Not B:

  *

  Address Windowing Extensions (AWE) is a Microsoft Windows application programming interface that allows a 32-bit software application to access more physical memory than it has virtual address space.

  *

  Address Windowing Extensions (AWE) is a set of extensions that allows an application to quickly manipulate physical memory greater than 4GB. Certain data-intensive applications, such as database management systems and scientific and engineering software, need access to very large caches of data. In the case of very large data sets, restricting the cache to fit within an application's 2GB of user address space is a severe restriction.

• Question 159:

  You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The package and all three Execute SQL tasks have their TransactionOption property set to Supported.

  You need to ensure that if any of the Execute SQL tasks fail, all three tasks will roll back their changes.

  What should you do?

  A. Move the three Execute SQL tasks into a Sequence container.
  B. Move the three Execute SQL tasks into a Foreach Loop container.
  C. Change the TransactionOption property of all three Execute SQL tasks to Required.
  D. Change the TransactionOption property of the package to Required.
  D. Change the TransactionOption property of the package to Required.

  ---

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

  http://msdn.microsoft.com/enus/library/microsoft.sqlserver.dts.runtime.dtstransactionoption.aspx

• Question 160:

  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 developing a Microsoft SQL Server Integration Services (SSIS) package.

  You need to ensure that the package records the current Log Sequence Number (LSN) in the source database before the package begins reading source tables.

  Which SSIS Toolbox item should you use?

  A. CDC Control task
  B. CDC Splitter
  C. Union All
  D. XML task
  E. Fuzzy Grouping
  F. Merge
  G. Merge Join
  A. CDC Control task

  ---

  The CDC Control task is used to control the life cycle of change data capture (CDC) packages. It handles CDC package synchronization with the initial load package, the management of Log Sequence Number (LSN) ranges that are processed in a run of a CDC package.

  References: https://docs.microsoft.com/en-us/sql/integration-services/control-flow/cdc-control-task