Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 121:

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

  The project will be deployed to an SSIS catalog folder where Environments have already been created. You need to deploy the project. What should you do?

  A. Use an event handler for OnError for each data flow task.
  B. Use an event handler for OnTaskFailed for the package.
  C. View the job history for the SQL Server Agent job.
  D. View the All Messages subsection of the All Executions report for the package.
  E. Store the System::SourceID variable in the custom log table.
  F. Store the System::ServerExecutionID variable in the custom log table.
  G. Store the System::ExecutionInstanceGUID variable in the custom log table.
  H. Deploy the project by using dtutil.exe with the /COPY DTS option.
  I. Deploy the project by using dtutil.exe with the /COPY SQL option.
  J. Deploy the .ispac file by using the Integration Services Deployment Wizard.
  J. Deploy the .ispac file by using the Integration Services Deployment Wizard.

• Question 122:

  A SQL Server Integration Services (SSIS) package imports daily transactions from several files into a SQL Server table named Transaction. Each file corresponds to a different store and is imported in parallel with the other files. The data flow tasks use OLE DB destinations in fast load data access mode.

  The number of daily transactions per store can be very large and is growing. The Transaction table does not have any indexes.

  You need to minimize the package execution time.

  What should you do?

  A. Partition the table by day and store.
  B. Create a clustered index on the Transaction table.
  C. Run the package in Performance mode.
  D. Increase the value of the Row per Batch property.
  D. Increase the value of the Row per Batch property.

  ---

  Data Access Mode - This setting provides the 'fast load' option which internally uses a BULK INSERT statement for uploading data into the destination table instead of a simple INSERT statement (for each single row) as in the case for other

  options.

  BULK INSERT parameters include:

  - ROWS_PER_BATCH =rows_per_batchIndicates the approximate number of rows of data in the data file. By default, all the data in the data file is sent to the server as a single transaction, and the number of rows in the batch is unknown to the query optimizer. If you specify ROWS_PER_BATCH (with a value > 0) the server uses this value to optimize the bulk-import operation. The value specified for ROWS_PER_BATCH should approximately the same as the actual number of rows.

• Question 123:

  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. You have decided to make use of Expression Task.

  Which of the following is TRUE with regards to Expression Task?

  A. It creates and evaluates expressions that set variable values at runtime.
  B. It provides code to perform functions that are not available in the built-in tasks and transformations that SQL Server Integration Services provides.
  C. It runs an application or batch file as part of a SQL Server Integration Services package workflow.
  D. It allows you to run SQL statements during package execution.
  A. It creates and evaluates expressions that set variable values at runtime.

• Question 124:

  A SQL Server Integration Services (SSIS) package is designed to download data from a financial database hosted in SQL Azure.

  The connection string to the financial database is defined as a project parameter named FinConStr. The parameter value must be stored securely and must be set explicitly every time the package is executed.

  You need to configure the parameter to meet the requirements.

  What should you do? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

  Hot Area:

  

  

• Question 125:

  You are the data steward for a Business Intelligence project.

  You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.

  You need to produce the CSV file with the least amount of development effort.

  What should you do?

  A. Create an Integration Services package and use a DQS Cleansing transform.
  B. Create an Integration Services package and use a Fuzzy Lookup transform.
  C. Create a Data Quality Project.
  D. Create an Integration Services package and use a Fuzzy Grouping transform.
  E. Create a Master Data Services (MDS) model.
  C. Create a Data Quality Project.

  ---

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

• Question 126:

  You work as a senior 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 running a training exercise for Microsoft SQL Server 2012 junior developers. You are discussing data quality projects.

  Which of the following are benefits of data quality projects? (Choose all that apply.)

  A. It enables you to perform data cleansing on your source data by using the knowledge in a DQS knowledge base.
  B. It allows you to perform data matching on your source data by using the knowledge in a DQS knowledge base.
  C. It allows you to perform data matching on your source data by using the matching policy in a knowledge base.
  D. It enables you to perform data cleansing on your source data by using the matching policy in a knowledge base.
  A. It enables you to perform data cleansing on your source data by using the knowledge in a DQS knowledge base.
  C. It allows you to perform data matching on your source data by using the matching policy in a knowledge base.

• Question 127:

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

  The package is allowed to connect to only one database. An Environment variable contains the name of the database.

  The OLE DB project connection manager has been parameterized.

  You need to configure the connection manager property to accept the value of the Environment variable.

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

  

  Hot Area:

  

  

• Question 128:

  Which of the following statements are true? (Choose all that apply.)

  A. An SSIS package can contain one or more SSDT solutions, each performing a specific data management operation.
  B. An SSIS project can contain one or more SSIS packages.
  C. An SSIS project can contain exactly one SSIS package.
  D. SSIS packages contain programmatic logic used in data movements and data transformation operations.
  B. An SSIS project can contain one or more SSIS packages.
  D. SSIS packages contain programmatic logic used in data movements and data transformation operations.

  ---

  References: https://docs.microsoft.com/en-us/sql/integration-services/integration-services-ssis-projects-and-solutions https://mindmajix.com/ssis/scripting-in-ssis

• Question 129:

  You are using the Master Data Services (MDS) Add-in for Excel to configure the entities in a model- The model consists of two entities: one named Customer and one named State.

  You opened the Customer entity.

  Currently, data stewards can enter any text value in the Customer entity's State attribute.

  You must restrict the entry of values in the State attribute to members defined in the State entity.

  You need to configure the State attribute of the Customer entity.

  Which command should you use? (To answer, select the appropriate command in the answer area.)

  Hot Area:

  

  

• Question 130:

  You are the administrator of a Data Quality Server.

  The server hosting the Data Quality Server had a hardware failure.

  You need to restore the Data Quality Server to another server. You have a recent backup of all the required databases.

  Which two actions should you perform? Each correct answer presents part of the solution.

  A. Execute the DQS_MAIN.internal_core.RestoreDQDatabases stored procedure with the appropriate parameter.
  B. Execute the DQS_MAIN.internal_core.InitServer stored procedure with the appropriate parameter.
  C. Restore only the DQS_MAIN and DQS_STAGING_DATA databases to another server as soon as possible.
  D. Restore the DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA databases to another server as soon as possible
  A. Execute the DQS_MAIN.internal_core.RestoreDQDatabases stored procedure with the appropriate parameter.
  D. Restore the DQS_MAIN, DQS_PROJECTS, and DQS_STAGING_DATA databases to another server as soon as possible

  ---

  Steps to restore DQS Databases:

  Restore DQS_MAIN database.

  Restore the DQS_PROJECTSdatabase.

  Restore the DQS_STAGING_DATA database.

  In Object Explorer, right-click the server, and then click New Query.

  In the Query Editor window, copy the following SQL statements, and replace with the password that you provided during the DQSinstallation for the database master key:USE [DQS_MAIN]GOEXECUTE [internal_core].

  [RestoreDQDatabases] ''GO

  Press F5 to execute the statements. Check the Results pane to verify that the statements have executed successfully.

  Note:

  Backup and restore of SQL Server databases are common operations that database administrators perform for preventing loss of data in a case of disaster by recovering data from the backup databases. Data Quality Server is primarily

  implemented by two SQL Server databases: DQS_MAIN and DQS_PROJECTS. The backup and restore procedures of the Data Quality Services (DQS) databases are similar to any other SQL Server databases.

  References: http://msdn.microsoft.com/en-gb/library/hh213068(v=sql.110).aspx