Microsoft 70-463 Online Practice Questions and Exam Preparation

Microsoft 70-463 Online Questions & Answers

• Question 41:

  You are troubleshooting an existing SQL Server Integration Services (SSIS) package. On several occasions, the package execution does not finish and no data seems to have been transferred.

  You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts.

  What should you do?

  A. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
  B. Use an msi file to deploy the package on the server.
  C. Run the package by using the dtexecui.exe utility and the SQL Log provider.
  D. Create a reusable custom logging component and use it in the SSIS project.
  E. Open a command prompt and run the dtutil /copy command.
  F. Configure the SSIS solution to use the Project Deployment Model.
  G. Add an OnError event handler to the SSIS project.
  H. Open a command prompt and run the gacutil command.
  I. Open a command prompt and run the dtexec /dumperror /conn command.
  J. Open a command prompt and run the dtexec /rep /conn command.
  C. Run the package by using the dtexecui.exe utility and the SQL Log provider.

  ---

  References: http://msdn.microsoft.com/en-us/library/ms141212.aspx http://www.mssqltips.com/sqlservertip/2450/ssis-package-deployment-model-in-sql-server-2012-part-1-of-2/ http://www.mssqltips.com/sqlservertip/2450/ssis-package-deployment-model-in-sql-server-2012-part-2-of-2/

• Question 42:

  You have a data warehouse.

  You need to create a SQL Server Integration Services (SSIS) package to meet the following requirements:

  The package must be deployed.

  The package must be parameterized.

  The package execution must be automated.

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

  Select and Place:

  

  

• Question 43:

  You have a database named Sales and a data warehouse named DataW.

  From Sales, you plan to bulk insert to a table in DataW that is more than 2 TB. The process will insert a minimum of 11 million rows simultaneously.

  You need to identify which data storage strategy must be used to minimize load times. Which data storage strategy should you identify?

  A. a nonclustered columnstore
  B. FileStream
  C. a durable In-Memory OLTP
  D. non-durable staging table
  E. a page-compressed heap
  D. non-durable staging table

  ---

  Incorrect Answers:

  C: In-Memory OLTP can significantly improve the performance of transaction processing, data ingestion and data load, and transient data scenarios. It does not fit a data warehouse scenario. References: http://henkvandervalk.com/how-to-load-data-fast-into-sql-server-2016

• Question 44:

  You are using SQL Server Data Tools to develop a SQL Server Integration Services (SSIS) project.

  The first package that you create in this project contains a package connection that accesses a Microsoft Excel file. Additional packages in the project must also access this file. You need to define and reuse the Excel file connection in all project packages.

  What should you do?

  A. Copy the package Connection Manager and paste it in the second package.
  B. Set the RetainSameConnection property of the package Connection Manager to True.
  C. Convert the package Connection Manager in the first package to a project Connection Manager.
  D. Convert the project to the Package Deployment model.
  C. Convert the package Connection Manager in the first package to a project Connection Manager.

  ---

  References: https://www.microsoftpressstore.com/articles/article.aspx?p=2224037andseqNum=2

• Question 45:

  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.

  

  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?

  

  IsolationLevel = ReadCommited TransactionOption = Required

• Question 46:

  You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. You add an Execute SQL task to the control flow. The task must execute a simple INSERT statement. The task has the following requirements:

  

  The INSERT statement must use the value of a string package variable. The variable name is StringVar.

  

  The Execute SQL task must use an OLE DB Connection Manager.

  In the Parameter Mapping tab of the Execute SQL task, StringVar has been added as the only parameter.

  You must configure the SQLStatement property of the Execute SQL task. Which SQL statement should you use?

  A. INSERT INTO dbo.Table (variablevalue) VALUES($StringVar)
  B. INSERT INTO dbo.Table (variablevalue) VALUES (0)
  C. INSERT INTO dbo.Table (variablevalue) VALUES (@0)
  D. INSERT INTO dbo.Table (variablevalue) VALUES (?)
  D. INSERT INTO dbo.Table (variablevalue) VALUES (?)

  ---

  References: http://msdn.microsoft.com/en-us/library/ms141003.aspx http://msdn.microsoft.com/en-us/library/ms140355.aspx http://msdn.microsoft.com/en-us/library/cc280502.aspx

• Question 47:

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

  The package uses custom functionality that accesses a SQL Server database. The custom functionality must be implemented by using Language Integrated Query (LINQ).

  You need to ensure that the LINQ code can be debugged at design time.

  What should you select from the SSIS Toolbox? (To answer, drag the appropriate item to the correct location in the answer area.)

  Select and Place:

  

  

• Question 48:

  You are maintaining a Data Quality Services (DQS) environment. The production server failed and a new server has been set up. The DQS databases are restored to a new server. All the appropriate permissions are granted.

  DQS users are experiencing issues connecting to the new Data Quality Server.

  You need to enable users to connect to the new server.

  Which Surface Area Configuration property should you enable?

  A. AdHocRemoteQueriesEnabled
  B. SoapEndpointsEnabled
  C. ClrIntegrationEnabled
  D. RemoteDacEnabled
  E. OleAutomationEnabled
  F. XpCmdShellEnabled
  C. ClrIntegrationEnabled

• Question 49:

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

  The project is deployed to a single SSIS catalog, and transfers data to and from multiple databases hosted on SQL Server.

  The project must be configured to be able to export data to and from five different production servers that run SQL Server. Each target server requires different values for connection strings and parameters in the SSIS project.

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

  What should you do?

  A. For each target server, create separate registry entry configurations. Select the registry entry at package execution time.
  B. For each target server, create separate Environments in the SSIS catalog of the host SQL Server SSIS instance. Select the appropriate Environment at package execution time.
  C. Create one SSIS catalog Environment. Change the values of each Environment variable at package execution time.
  D. For each target server, create a separate XML configuration file. Select the XML configuration file at package execution time.
  B. For each target server, create separate Environments in the SSIS catalog of the host SQL Server SSIS instance. Select the appropriate Environment at package execution time.

• Question 50:

  You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model. All packages in the project must log custom messages.

  You need to produce reports that combine the custom log messages with the system-generated log messages. What should you do?

  A. Use an event handler for OnError for the package.
  B. Use an event handler for OnError for each data flow task.
  C. Use an event handler for OnTaskFailed for the package.
  D. View the job history for the SQL Server Agent job.
  E. View the All Messages subsection of the All Executions report for the package.
  F. Store the System::SourceID variable in the custom log table.
  G. Store the System::ServerExecutionID variable in the custom log table.
  H. Store the System::ExecutionInstanceGUID variable in the custom log table.
  I. Enable the SSIS log provider for SQL Server for OnError in the package control flow.
  J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow,
  G. Store the System::ServerExecutionID variable in the custom log table.