70-463 | Microsoft 70-463 Dumps 2021

NEW QUESTION 1
You are designing a SQL Server Integration Services (SSIS) package that uploads a file to a table named Orders in a SQL Azure database.
The company's auditing policies have the following requirements:
• An entry must be written to a dedicated SQL Server log table named OrderLog.
• The entry must be written as soon as the file upload task completes. You need to meet the company's policy requirements.
Which event handler should you use?

• A. OnPostExecute
• B. OnWarning
• C. OnExecStatusChanged
• D. OnVariableValueChanged

Answer: A

Explanation: Reference: http://msdn.microsoft.com/en-us/library/ms140223.aspx

NEW QUESTION 2
You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit.


Confidential information about the employees is stored in a separate table named EmployeeDat

• A. One record exists within EmployeeData for each record in the Employee tabl
• B. You need to assign the appropriate constraints and table properties to ensure data integrity and visibilit
• C. On which column in the Employee table should you create a unique constraint?
• D. DateHired
• E. DepartmentID
• F. EmployeelD
• G. EmployeeNum
• H. FirstName
• I. JobTitle
• J. LastName
• K. MiddleName
• L. ReportsToID

Answer: D

NEW QUESTION 3
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads rows from a source system and compares them to rows in a destination system. New rows will be inserted and changed rows will be updated.
You have used a Lookup transformation and a Conditional Split transformation. The Lookup transformation joins the source and destination table on the business key, and includes all columns from the destination table in the data flow output. The Conditional Split transformation inspects the destination columns and directs data flow to either insert new records or update existing records. You need to configure the Lookup transformation to ensure that all records flow to the Conditional Split transformation, regardless of whether the rows match an existing row in the destination table. Which setting should you select? (To answer, select the appropriate option in the answer area.)

Answer:

Explanation:

NEW QUESTION 4
You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables.
You have the following requirements:
• The deployment model must support changing the content of connection strings by using parameters at execution time.
• The deployment model must automatically log events to the SSISDB database.
• Maximize performance at execution time.
You need to design a solution that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A. Use a project deployment mode
• B. Modify connection manager properties to use project parameters.
• C. Use a package deployment mode
• D. Save each SSIS package to a file share that can be accessed from all environments.
• E. Use a package deployment mode
• F. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package.
• G. Use a project deployment mode
• H. Modify connection manager properties to use package parameters.

Answer: A

NEW QUESTION 5
DRAG DROP
You are designing an extract, transform, load (ETL) process with SQL Server Integration Services (SSIS). Two packages, Package A and Package B, will be designed. Package A will execute Package B. Both packages must reference a file path corresponding to an input folder where files will be located for further processing.
You need to design a solution so that the file path can be easily configured with the least administrative and development effort.
Which four actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Answer:

Explanation: Reference:
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

NEW QUESTION 6
You are designing a data warehouse that uses SQL Server 2012.
The data warehouse contains a table named factSales that stores product sales. The table has a clustered index on the primary key, four foreign keys to dimension tables, and an aggregate column for sales totals. All key columns use the INT data type, and the aggregate column uses the MONEY data type.
You need to increase the speed of data retrieval from the factSales table. Which index type should you add to the table?

• A. Full text
• B. Spatial
• C. Non-clustered
• D. Clustered

Answer: C

NEW QUESTION 7
DRAG DROP
You are building a SQL Server Integration Services (SSIS) package to load data from all files that are automatically copied to a directory each night through an external FTP process.
You need to load data from all copied files to a destination table in SQL Server.
Which three steps should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Answer:

Explanation:
References:
http://msdn.microsoft.com/en-us/library/ms141724.aspx http://msdn.microsoft.com/en-us/library/ms137728.aspx

NEW QUESTION 8
Which statements best describe SQL Server Development Tools (SSDT)? (Choose all that apply.)

• A. SSDT is an extension of the SQL Server Management Studio that can be used to create SSIS packages by means of a special wizard.
• B. SSDT is a special edition of the SQL Server Management Studio, designed to provide an improved userexperience to developers who are not particularly familiar with database administration.
• C. SSDT is a special edition of Visual Studio, distributed with SQL Server 2012, providing a rich databasedevelopment tool set.
• D. SSDT is a new service in SQL Server 2012 that can be used to perform SQL Server maintenance tasks,such as data movements and similar data management processe

Answer: C

NEW QUESTION 9
You are writing a SQL Server Integration Services (SSIS) package that transfers data from a legacy system.
Data integrity in the legacy system is very poor. Invalid rows are discarded by the package but must be logged to a CSV file for auditing purposes.
You need to establish the best technique to log these invalid rows while minimizing the amount of development effort.
What should you do?

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

Answer: A

Explanation: Reference:
http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/hh230989.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx

NEW QUESTION 10
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. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
• 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.
• K. Configure the output of a component in the package data flow to use a data ta

Answer: C

Explanation: 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/

NEW QUESTION 11
You have a data warehouse that contains all of the sales data for your company. The data warehouse contains several SQL Server Integration Services (SSIS) packages.
You need to create a custom report that contains the total number of rows processed in the package and the time required for each package to execute. Which view should you include in the report?

• A. catalog.executable_statistics
• B. catalog.execution_data_taps
• C. catalog.event_messages
• D. catalog.execution_data_statistics

Answer: D

Explanation: The catalog.execution_data_statistics view displays a row each time a data flow component sends data to a downstream component, for a given package execution. The information in this view can be used to compute the data throughput for a component. Fields in this view include:
created_time The time when the values were obtained. rows_sent The number of rows sent from the source component. References:
https://docs.microsoft.com/en-us/sql/integration-services/system-views/catalog-executiondatastatistics

NEW QUESTION 12
You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel workbook data into a Microsoft Azure SQL Database database. The package has been deployed to a 64-bit production SQL Server.
The package fails when executed on the production server.
You need to ensure that the package can load the 32-bit Excel workbook data without errors. What should you do?

• A. Replace the SSIS Excel source with a SQL Server source.
• B. Enable Address Windowing Extensions (AWE) for the local SQL Server instance.
• C. Install a 32-bit instance of SSIS side-by-side with the 64-bit version.
• D. Execute the package by using the 32-bit run-time optio

Answer: D

Explanation: If the computer has a 32-bit version of Office, then you have to install the 32-bit version of the components, and you also have to ensure that you run the package in 32-bit mode. References:
https://docs.microsoft.com/en-us/sql/integration-services/connection-manager/connect-to-anexcel- workbook

NEW QUESTION 13
You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart.
You have the following requirements:
• Ensure that the process supports the creation of an exception report that details
possible duplicate key values, null ratios within columns, and column-length distributions of values.
• Ensure that users can generate the exception report in an XML format.
• Use the minimum development effort.
You need to design the SSIS solution to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A. Use a Data Profiling tas
• B. Use a Data Flow task to extract the XML output of the Data Profiling task into a SQL Server tabl
• C. Query the table to view the exceptions.
• D. Use Data Flow tasks to process the clean data.
• E. Use a Data Profiling tas
• F. Read the exceptions in Data Profile Viewer.
• G. Design a stored procedure that examines data for common dirty data pattern
• H. Use an Execute SQL task.

Answer: C

NEW QUESTION 14
You are developing a SQL Server Integration Services (SSIS) project that copies a large amount of rows from a SQL Azure database. The project uses the Package Deployment Model. This project is deployed to SQL Server on a test server.
You need to ensure that the project is deployed to the SSIS catalog on the production server. What should you do?

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

Answer: I

Explanation: References:
http://msdn.microsoft.com/en-us/library/hh231102.aspx http://msdn.microsoft.com/en-us/library/hh213290.aspx http://msdn.microsoft.com/en-us/library/hh213373.aspx

NEW QUESTION 15
You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The three Execute SQL tasks modify products in staging tables in preparation for a data warehouse load.
The package and all three Execute SQL product tasks have their TransactionOption property set to Supported.
You need to ensure that if any of the three Execute SQL product tasks fail, all three tasks will roll back their changes.
What should you do?

• A. Change the TransactionOption property of the package to Required.
• B. Change the TransactionOption property of all three Execute SQL product tasks to Required.
• C. Move the three Execute SQL product tasks into a Foreach Loop container.
• D. Move the three Execute SQL product tasks into a Sequence containe

Answer: A

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms137690.aspx http://msdn.microsoft.com/en-us/library/ms141144.aspx

NEW QUESTION 16
You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks. The package and all three Execute SQL tasks have their TransactionOption
property set to Supported.
You need to ensure that if any of the Execute SQL tasks fail, all three tasks will roll back their changes. What should you do?

• A. Move the three Execute SQL tasks into a Sequence container.
• B. Move the three Execute SQL tasks into a Foreach Loop container.
• C. Change the TransactionOption property of all three Execute SQL tasks to Required.
• D. Change the TransactionOption property of the package to Required.

Answer: D

Explanation: Reference:
http://msdn.microsoft.com/en-us/library/ms137749.aspx
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtstransactionoption.aspx

NEW QUESTION 17
DRAG DROP
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.

Answer:

Explanation:

NEW QUESTION 18
You are developing a SQL Server Integration Services (SSIS) package that loads data from a source transactional tables in a data warehouse.
You discover that one of the data flows loads incorrect data to one of the destination tables. You need to identify the cause of the incorrect data flow. Which tool should you use?

• A. SSIS logging.
• B. a breakpoint
• C. a data viewer
• D. the locals windo

Answer: C