Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 171:

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

  The project contains many packages. It is deployed on a server named SQLTest1. The project will be deployed to several servers that run SQL Server 2012.

  The project accepts one required parameter. The data type of the parameter is a string.

  A SQL Agent job is created that will call the Loading.dtsx package in the project. A job step is created for the SSIS package.

  The job must pass the value of an SSIS Environment Variable to the project parameter.

  The value of the Environment Variable must be configured differently on each server that runs SQL Server. The value of the Environment Variable must provide the server name to the project parameter.

  You need to configure SSIS on the SQLTest1 server to pass the Environment Variable to the package.

  Which four actions should you perform in sequence by using SQL Server Management Studio?

  (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

• Question 172:

  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 installing the Business Intelligence features. You are currently performing the post-installation of the Data Quality Server component of SQL Server Data Quality Services (DQS).

  Which of the following actions should you take to conclude the Data Quality Server installation?

  A. You should consider executing the DQSInstaller.exe file.
  B. You should consider executing the SSISUpgrade.exe file.
  C. You should consider executing the dtexecui.exe file.
  D. You should consider executing the dtutil.exe file.
  E. You should consider executing the gacutil.exe file.
  A. You should consider executing the DQSInstaller.exe file.

• Question 173:

  You are designing a data warehouse that uses SQL Server 2012. You are preparing to update the contents of a fact table that contains a non-clustered columnstore index.

  You need to run an update statement against the table.

  What should you do first?

  A. Pause the columnstore index.
  B. Change the recovery model of the database to Bulk-logged.
  C. Change the non-clustered columnstore index to be a clustered columnstore index.
  D. Drop the columnstore index.
  D. Drop the columnstore index.

• Question 174:

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

  You need to design a package to change a variable value during package execution by using the least amount of development effort.

  What should you use?

  A. Expression task
  B. Script task
  C. Execute SQL task
  D. Execute Process task
  E. Term Extraction transformation
  A. Expression task

  ---

  The Expression Task creates and evaluates expressions that set variable values at runtime, using the Expression Builder. References: http://msdn.microsoft.com/en-us/library/hh213137.aspx

• Question 175:

  You are deploying a new SQL Server Integration Services (SSIS) package to several servers.

  The package must meet the following requirements:

  

  .NET Common Language Runtime (CLR) integration in SQL Server must not be enabled.

  

  The Connection Managers used in the package must be configurable without editing the package.

  

  The deployment procedure must be automated as much as possible.

  You need to set up a deployment strategy that meets the requirements.

  What should you do?

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

• Question 176:

  You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on ServerA. The package includes a data flow and is executed on ServerB. The destination table has its own identity column.

  The destination data load has the following requirements:

  

  The identity values from the source table must be used.

  

  Default constraints on the destination table must be ignored.

  

  Batch size must be 100,000 rows.

  You need to add a destination and configure it to meet the requirements.

  Which destination should you use?

  A. OLE DB Destination with Fast Load
  B. SQL Server Destination
  C. ADO NET Destination without Bulk Insert
  D. ADO NET Destination with Bulk Insert
  E. OLE DB Destination without Fast Load
  A. OLE DB Destination with Fast Load

  ---

  References: http://msdn.microsoft.com/en-us/library/ms141237.aspx http://msdn.microsoft.com/en-us/library/ms139821.aspx http://msdn.microsoft.com/en-us/library/ms141095.aspx

• Question 177:

  You deploy a server that has SQL Server installed.

  You deploy a SQL Server Integration Services (SSIS) package to the server.

  You need to automate the execution of the package. The solution must ensure that you receive a notification if the package fails to execute.

  In which order should you perform all the actions? To answer, move all of the actions to the answer area and arrange them in the correct order.

  NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

  Select and Place:

  

  

• Question 178:

  You administer a SQL Server Integration Services (SSIS) solution in the SSIS catalog. A SQL Server Agent job is used to execute a package daily with the basic logging level.

  Recently, the package execution failed because of a primary key violation when the package inserted data into the destination table.

  You need to identify all previous times that the package execution failed because of a primary key violation.

  What should you do?

  A. Use an event handler for OnError for the package.
  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.
  D. View the All Messages subsection of the All Executions report for the package.

• Question 179:

  If you want to switch content from a nonpartitioned table to a partition of a partitioned table, what conditions must the nonpartitioned table meet? (Choose all that apply.)

  A. It must have the same constraints as the partitioned table.
  B. It must have the same compression as the partitioned table.
  C. It must be in a special PartitionedTables schema.
  D. It must have a check constraint on the partitioning column that guarantees that all of the data goes to exactly one partition of the partitioned table.
  E. It must have the same indexes as the partitioned table.
  A. It must have the same constraints as the partitioned table.
  B. It must have the same compression as the partitioned table.
  D. It must have a check constraint on the partitioning column that guarantees that all of the data goes to exactly one partition of the partitioned table.
  E. It must have the same indexes as the partitioned table.

  ---

  References: https://technet.microsoft.com/en-us/library/ms191160(v=sql.105).aspx

• Question 180:

  Your team is creating SQL Server Integration Services (SSIS) packages that have several dependencies. The packages use parameters for configuration purposes.

  Your company's IT policies include the following change control requirements:

  

  After a package is ready for deployment, your team must hand over the process to junior IT personnel.

  

  The process must guarantee that when a package has been validated, that same package (and all its dependencies) is deployed to production.

  

  The process must be repeatable and reliable and must be executed with the least administrative and training effort by junior IT personnel.

  You need to use the most appropriate deployment unit to satisfy the company policies, while minimizing issues such as incorrect version or configuration. Which type of deployment unit should you use?

  A. dtsx
  B. SSIS deployment manifest
  C. msi
  D. ispac
  D. ispac