Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 11:

  You are designing a SQL Server Integration Services (SSIS) package. The package moves order-related data to a staging table named Order. Every night the staging data is truncated

  and then all the recent orders from the online store database are inserted into the staging table.

  Your package must meet the following requirements:

  If the truncate operation fails, the package execution must stop and report an error.

  If the Data Flow task that moves the data to the staging table fails, the entire refresh operation must be rolled back.

  For auditing purposes, a log entry must be entered in a SQL log table after each execution of the Data Flow task.

  The TransactionOption property for the package is set to Required.

  You need to design the package to meet the requirements.

  How should you design the control flow for the package? (To answer, drag the appropriate setting from the list of settings to the correct location or locations in the answer area.)

  Select and Place:

  

  

• Question 12:

  You are designing a SQL Server Integration Services (SSIS) package that will load data from a source system to a data mart.

  You need to combine conditionally the value of two project parameters to a package variable.

  What should you do?

  A. Configure an event handler.
  B. Set the data type property of the variable.
  C. Set the Expression property of the variable.
  D. Modify the namespace of the variable.
  C. Set the Expression property of the variable.

  ---

  The Expression property of a variable specifies the expression that is assigned to the variable.

  References: https://docs.microsoft.com/en-us/sql/integration-services/integration-services-ssis-variables

• Question 13:

  You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server 2012 database.

  The name of the target instance is passed to the package via an Environment variable.

  The Environment variable provides the server name to the Project parameter named ServerName.

  The OLE DB Project Connection Manager has been parameterized.

  You need to configure the Connection Manager property to accept the parameter.

  Which property and parameter should you use? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

  Hot Area:

  

  

• Question 14:

  You use SQL Server Integration Services (SSIS) for extract, transformation, load (ETL) processing. Issues concerning addresses are discovered in the data warehouse that you manage.

  Validation must separate the addresses into three categories: Valid addresses Autocorrected addresses Invalid addresses

  You need to enhance the SSIS packages to perform address validation by using an external service.

  Which transformation should you use? (To answer, drag the appropriate transformation from the list of options to the correct location or locations in the answer area.)

  Select and Place:

  

  

• Question 15:

  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

  ---

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

• Question 16:

  Some of the data your company processes is sent in from partners via email.

  How would you configure an SMTP connection manager to extract files from email messages?

  A. In the SMTP connection manager, configure the OperationMode setting to Send And Receive.
  B. It is not possible to use the SMTP connection manager in this way, because it can only be used by SSIS to send email messages.
  C. The SMTP connection manager supports sending and receiving email messages by default, so no additional configuration is necessary.
  D. It is not possible to use the SMTP connection manager for this; use the IMAP (Internet Message Access Protocol) connection manager instead.
  B. It is not possible to use the SMTP connection manager in this way, because it can only be used by SSIS to send email messages.

  ---

  References: https://docs.microsoft.com/en-us/sql/integration-services/connection-manager/smtp-connection-manager

• Question 17:

  You are designing a data warehouse hosted on SQL Azure. The data warehouse currently includes the dimUser and dimDistrict dimension tables and the factSales fact table. The dimUser table contains records for each user permitted to run

  reports against the warehouse; and the dimDistrict table contains information about sales districts.

  The system is accessed by users from certain districts, as well as by area supervisors and users from the corporate headquarters.

  You need to design a table structure to ensure that certain users can see sales data for only certain districts. Some users must be permitted to see sales data from multiple districts.

  What should you do?

  A. Add a district column to the dimUser table.
  B. Partition the factSales table on the district column.
  C. Create a userDistrict table that contains primary key columns from the dimUser and dimDistrict tables.
  D. For each district, create a view of the factSales table that includes a WHERE clause for the district.
  C. Create a userDistrict table that contains primary key columns from the dimUser and dimDistrict tables.

• Question 18:

  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. Install the Analysis Services OLE DB Provider.
  B. Make the data available for DQS operations.
  C. Run the Data Quality Server Installer.
  D. Install ADOMD.NET.
  C. Run the Data Quality Server Installer.

  ---

  Post-Installation Tasks

  After you complete the SQL Server installation wizard, you must perform additional steps mentioned in this section to complete your Data Quality Server installation, configure it, and then use it.

  To complete the Data Quality Server installation, run the DQSInstaller.exe file

  References:

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

• Question 19:

  You are loading a dataset into SQL Server. The dataset contains numerous duplicates for the Artist and Song columns.

  The values in the Artist column in the dataset must exactly match the values in the Artist domain in the knowledge base. The values in the Song column in the dataset can be a close match with the values in the Song domain.

  You need to use SQL Server Data Quality Services (DQS) to define a matching policy rule to identify duplicates.

  How should you configure the Rule Editor? (To answer, drag the appropriate answers to the answer area.)

  Select and Place:

  

  

• Question 20:

  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.

  You are in the process of creating a SQL Server Integration Services (SSIS) package. The package will be configured to make use of the GenerateAndPersistNewIndex Fuzzy Lookup transformation option.

  Which of the following is the purpose of this option?

  A. It allows for a current index to be reused.
  B. It allows for a new index to be created, without saving it.
  C. It allows for a new index to be created and saved, without maintaining it.
  D. It allows for a new index to be created, saved, and maintained.
  C. It allows for a new index to be created and saved, without maintaining it.