Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 91:

  You are developing a SQL Server Integration Services (SSIS) package that imports unsorted data into a data warehouse hosted on SQL Azure. You have the following requirements:

  A destination table must contain all of the data in two source tables.

  Duplicate records must be inserted into the destination table.

  You need to develop a data flow that imports the data while meeting the requirements.

  How should you develop the data flow? (To answer, drag the appropriate transformation from the list of transformations to the correct location in the answer area.)

  Select and Place:

  

  

• Question 92:

  You are developing a SQL Server Integration Services (SSIS) package that loads data into a data warehouse hosted on Windows Azure SQL Database.

  You must combine two data sources together by using the ProductID column to provide complete details for each record. The data retrieved from each data source is sorted in ascending order by the ProductID column.

  You need to develop a data flow that imports the data while meeting the requirements.

  How should you develop the data flow? (To answer, drag the appropriate transformation from the list of transformations to the correct location in the answer area.)

  Select and Place:

  

  

• Question 93:

  You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed.

  You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables.

  You need to configure the SQL Server environment to support these packages.

  What should you do?

  A. Create SSIS configuration files for the packages.
  B. Create an Integration Services catalog.
  C. Install Data Quality Services.
  D. Install Master Data services.
  B. Create an Integration Services catalog.

  ---

  References: 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 94:

  You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads data from a source system that uses the SQL Server change data capture (CDC) feature.

  You have added a CDC Source component to the data flow to read changed data from the source system.

  You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations.

  Which data flow transformation should you use?

  A. DQS Cleansing
  B. Conditional Split
  C. Audit
  D. Merge
  B. Conditional Split

  ---

  Split the changes into inserts, updates, and deletes for processing.

  To split the changes, use a Conditional Split transformation to direct inserts, updates, and deletes to different outputs for appropriate processing.

  References: https://docs.microsoft.com/en-us/sql/integration-services/change-data-capture/change-data-capture-ssis?view=sql-server-2017

• Question 95:

  You work as a database developer at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.

  ABC.com has two database servers, named ABC-SR13 and ABC-SR14. You are in the process of creating a SQL Server Integration Services (SSIS) package that must be able to load data into a SQL Server table on ABC-SR13, and is

  executed on ABC-SR14. The package also contains a data flow.

  You have decided to make use of the OLE DB Destination with Fast Load to add a destination.

  Which of the following are fast load options that you can specify in the OLE DB Destination Editor for the destination? (Choose all that apply.)

  A. You can specify that a table-level lock for the duration of the bulk load operation must be acquired.
  B. You can specify that identity values from the imported data file must be discarded.
  C. You can specify that common values assigned by SQL Server must be used.
  D. You can specify that a null value must be retained during the bulk load operation.
  A. You can specify that a table-level lock for the duration of the bulk load operation must be acquired.
  D. You can specify that a null value must be retained during the bulk load operation.

• Question 96:

  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. an Execute SQL task that executes a MERGE statement on the database
  B. an Aggregate component
  C. a Merge Join component
  D. a Data Conversion component
  C. a Merge Join component

  ---

  Type of Slowly Changing DimensionsSlowly Changing Dimensions are categorized into three types named: Type 1, Type 2, and Type3. The Type 1 SCD does not maintain the history of changing attributes, it overwrites values of the

  attributes. Type 2 maintains historical values for changing attributes. Type 3 that we do not use much maintains separate columns for changed attributes. SSIS SCD wizard supports both Type 1 and Type 2.

  Using MERGE instead of SCD wizardReplacement of SCD wizard with MERGE is not a straightforward technique. If the SCD has both Type 1 and Type 2 types attributes, they need to be handled separately.

  References: https://docs.microsoft.com/en-us/sql/integration-services/data-flow/transformations/slowly-changing-dimension-transformation?view=sql-server-2017

• Question 97:

  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. Change the Exhaustive property to True.
  B. Change the similarity threshold to 0.55.
  C. Change the similarity threshold to 0.40.
  D. Increase the maximum number of matches per lookup.
  B. Change the similarity threshold to 0.55.

  ---

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

• Question 98:

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

  The package sources data from an HTML web page that lists product stock levels.

  You need to implement a data flow task that reads the product stock levels from the HTML web page.

  Which data flow source should you use?

  A. XML source
  B. Custom source component
  C. Flat File source
  D. Raw File source
  B. Custom source component

  ---

  Integration Services can extract and transform data from a wide variety of sources. You can extract data from flat files, XML files, Microsoft Excel workbooks, and files that contain raw data. You can also extract data by accessing tables and views in databases and by running queries. You can also write custom sources.

  References: https://docs.microsoft.com/en-us/sql/integration-services/data-flow/data-flow?view=sql-server-2017

• Question 99:

  You are developing a SQL Server Integration Services (SSIS) project to read and write data from a Windows Azure SQL Database database to a server that runs SQL Server 2012.

  The connection will be used by data flow tasks in multiple SSIS packages. The address of the target Windows Azure SQL Database database will be provided by a project parameter.

  You need to create a solution to meet the requirements by using the least amount of administrative effort.

  What should you do?

  A. Add a SQLMOBILE connection manager to each package.
  B. Add an ADO.NET project connection manager.
  C. Add a SQLMOBILE project connection manager.
  D. Add an ADO.NET connection manager to each data flow task.
  E. Add a SQLMOBILE connection manager to each data flow task.
  F. Add an ADO.NET connection manager to each package.
  B. Add an ADO.NET project connection manager.

  ---

  References: http://www.databasejournal.com/features/mssql/windows-azure-sql-database-uploading-data-by-using-sql-server-integration-services.html

• Question 100:

  You work as a senior database administrator at ABC.com. The ABC.com network consists of a single domain named ABC.com. ABC.com makes use of Microsoft SQL Server 2012 in their environment.

  You have recently created a SQL Server Integration Services (SSIS) package that is hosted by the file system. Subsequent to deploying the package, you want to execute it using a Transact-SQL statement.

  Which of the following is the Transact-SQL statement you should use?

  A. catalog.startup_package
  B. catalog.start_package
  C. catalog.validate_package
  D. catalog.create_execution
  B. catalog.start_package