Microsoft 70-463 Exam Questions 2021

NEW QUESTION 1
To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a logging methodology is put in place.
The methodology has the following requirements:
• The deployment process must be simplified.
• All the logs must be centralized in SQL Server.
• Log data must be available via reports or T-SQL.
• Log archival must be automated.
You need to configure a logging methodology that meets the requirements while minimizing the amount of deployment and development effort.
What should you do?

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

Answer: B

Explanation: Reference:
http://msdn.microsoft.com/en-us/library/ms140246.aspx http://msdn.microsoft.com/en-us/library/ms180378(v=sql.110).aspx

NEW QUESTION 2
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package. The data source for the data flow task is a table that has been configured as a change data capture (CDC) table. You are using a CDC Source component to obtain the CDC data.
The data source will be polled once per hour. The data is updated with multiple important status changes per minute.
For each captured data change, the before and after values must be included. You need to configure the CDC Source component.
Which CDC processing mode should you select? (To answer, configure the appropriate option in the dialog box in the answer area.)

Answer:

Explanation:

NEW QUESTION 3
You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages.
All packages in the project must connect to the same database. The server name for the database must be set by using a parameter named ParamConnection when any package in the project is executed.
You need to develop this project with the least amount of development effort.
What should you do? (Each answer presents a part of the solution. Choose all that apply.)

• A. Create a package parameter named ConnectionName in each package.
• B. Edit each package Connection Manage
• C. Set the ConnectionName property to@[$Project::ParamConnection].
• D. Edit the project Connection Manager in Solution Explore
• E. Set the ConnectionName property to @ [$Project::ParamConnection].
• F. Set the Sensitive property of the parameter to True.
• G. Create a project parameter named ConnectionName.
• H. Set the Required property of the parameter to True.

Answer: CEF

Explanation:
C: From question: "The server name for the database must be set by using a parameter named ParamConnection when any package in the project is executed."
E: SSIS 2012 has introduced the concept of Project level connection managers. An SSIS project is generally more than one package. To simplify lives, the SSIS team now allows for the sharing of common resources across projects, connection managers being one of those resources.
F: When a parameter is marked as required, a server value or execution value must be specified for that parameter. Otherwise, the corresponding package does not execute. Although the parameter has a default value at design time, it will never be used once the project is deployed.
Note:
* Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project. Package parameters allow you to modify package execution without having to edit and redeploy the package.
Reference: Integration Services (SSIS) Parameters

NEW QUESTION 4
You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named ProductCode.
Some of the data to be loaded will reference products that need special processing logic in the data flow.
You need to enable separate processing streams for a subset of rows based on the source product code.
Which Data Flow transformation should you use?

• A. Multicast
• B. Conditional Split
• C. Script Task
• D. Data Conversion

Answer: B

Explanation: We use Conditional Split to split the source data into separate processing streams.
A Script Component (Script Component is the answer to another version of this question) could be used but this is not the same as a Script Task.

NEW QUESTION 5
You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day.
Referential integrity must be enforced declaratively.
You need to design a solution that can join a single time dimension to both fact tables. What should you do?

• A. Create a view on the sales table.
• B. Partition the fact tables by day.
• C. Create a surrogate key for the time dimension.
• D. Change the level of granularity in both fact tables to be the sam

Answer: D

NEW QUESTION 6
You are creating a SQL Server Master Data Services (MDS) model for a company.
The source data for the company is stored in a single table that contains the manager-to-subordinate relationships.
You need to create a hierarchy representing the organizational structure of the company. Which hierarchy type should you use?

• A. Organizational
• B. Recursive
• C. Non-Mandatory Explicit
• D. Many-to-Many

Answer: B

NEW QUESTION 7
DRAG DROP
You are building a fact table in a data warehouse.
The table must have a columnstore index. The table cannot be partitioned. You need to design the fact table and load it with data.
Which three 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:

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

Answer:

Explanation:

NEW QUESTION 9
You are designing a data warehouse with two fact tables. The first table contains sales per month and the second table contains orders per day.
Referential integrity must be enforced declaratively.
You need to design a solution that can join a single time dimension to both fact tables. What should you do?

• A. Join the two fact tables.
• B. Merge the fact tables.
• C. Change the level of granularity in both fact tables to be the same.
• D. Partition the fact tables by da

Answer: D

NEW QUESTION 10
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package. An Execute SQL task in the package checks product stock levels and sets a package variable named InStock to TRUE or FALSE depending on the stock level found.
After the successful execution of the Execute SQL task, one of two data flow tasks must run, depending on the value of the InStock variable.
You need to set the precedence constraints.
Which value for the evaluation operation should you use? (To answer, select the appropriate option for the evaluation operation in the answer area.)

Answer:

Explanation:

NEW QUESTION 11
You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation.
The reference data to be used in the transformation changes with every package execution. Common language runtime (CLR) integration cannot be enabled on the SQL Server database where the reference table is located.
You need to configure the Fuzzy Lookup transformation in the most efficient manner. What should you do?

• A. Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.
• B. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
• C. Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.
• D. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Edito

Answer: B

NEW QUESTION 12
You are deploying a new SQL Server Integration Services (SSIS) project to the test environment. A package in the project uses a custom task component.
You need to ensure that the custom object is deployed on the test environment correctly. What should you do?

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

Answer: D

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

NEW QUESTION 13
HOTSPOT
You are designing a SQL Server Integration Services (SSIS) package configuration strategy. The package configuration must meet the following requirements:
• Include multiple properties in a configuration.
• Support several packages with different configuration settings.
You need to select the appropriate configuration. Which configuration type should you use? To answer, select the appropriate option from the drop-down list in the dialog box.

Answer:

Explanation:

NEW QUESTION 14
You are developing a data flow transformation to merge two data sources. One source contains product data and the other source contains data about the country in which the product was manufactured. Both data sources contain a two-character CountryCode column and both use SQL Server. Both data sources contain an ORDER BY clause to sort the data by the CountryCode column in ascending order.
You use a Merge Join transformation to join the data.

You need to ensure that the Merge Join transformation works correctly without additional transformations. What should you do? (Each answer presents a part of the solution. Choose all that
apply.)

• A. set the appropriate SortKeyPosition properties on the data sources.
• B. set the ValidateExternalMetaData property on the Merge Join transformation to True.
• C. set the IsSorted property on both data sources.
• D. Set the MaxBuffersPerlnput property on the Merge Join transformation to 2.

Answer: AC

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms141775.aspx http://msdn.microsoft.com/en-us/library/ms137653.aspx http://siddhumehta.blogspot.com/2009/05/validateexternalmetadata-property.html http://msdn.microsoft.com/en-us/library/ms135950.aspx

NEW QUESTION 15
You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure database into a data warehouse.
The source system contains redundant or inconsistent dat

• A. When the package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a text file for further analysis.You need to establish the best technique to log these invalid rows while keeping the amount of development effort to a minimum.What should you do?
• B. Add an OnError event handler to the SSIS project.
• C. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
• D. Use an msi file to deploy the package on the server.
• E. Open a command prompt and run the gacutil command.
• F. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
• G. Open a command prompt and run the dtutil /copy command.
• H. Create a reusable custom logging component and use it in the SSIS project.
• 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. Open a command prompt and run the dtexec /rep /conn command.
• L. Open a command prompt and run the dtexec /dumperror /conn comman

Answer: I

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

NEW QUESTION 16
You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD).
You need to add a task or component to the package that allows you to implement the SCD logic. What should you use?

• A. a Data Conversion component
• B. an Execute SQL task that executes a MERGE statement on the database
• C. a Merge component
• D. an Expression task

Answer: C

Explanation: Note: Type of Slowly Changing Dimensions
Slowly Changing Dimensions are categorized into three types named: Type 1, Type 2, and Type3. The Type 1 SCD does not maintain the history of changing attributes, it overwrites values of the attributes. Type 2 maintains historical values for changing attributes. Type 3 that we do not use much maintains separate columns for changed attributes. SSIS SCD wizard supports both Type 1 and Type 2.
Using MERGE instead of SCD wizard
Replacement of SCD wizard with MERGE is not a straightforward technique. If the SCD has both Type 1 and Type 2 types attributes, they need to be handled separately.

NEW QUESTION 17
DRAG DROP
You are maintaining a SQL Server Integration Services (SSIS) package. The package uses custom functionality that is implemented in Microsoft Visual C#.
The implementation of the custom functionality changes overtime. The design of the package allows you to deploy new releases of the custom functionality without redeploying the entire package.
You need to implement and deploy an update to the custom functionality without requiring package redeployment.
Which three 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:

NEW QUESTION 18
You develop and deploy a SQL Server Integration Services (SSIS) package. The package is stored in the file system.
You need to execute the package without importing it to the SSIS server.
What should you use to execute the package? (Each correct answer presents a complete solution. Choose all that apply.)

• A. catalog.start_package
• B. dtexec
• C. SQL Server Management Studio
• D. SQL Server Agent

Answer: BD

Explanation: Ref: http://technet.microsoft.com/en-us/library/ms141708(v=sql.110).aspx

NEW QUESTION 19
You develop a SQL Server Integration Services (SSIS) project by using the Package Deployment Model. A package in the project extracts data from a Windows Azure SQL Database database. The package is deployed to SQL Server.
The package is not producing the desired results.
You need to generate the .mdmp and .tmp debug files in order to troubleshoot the issues. What should you do?

• A. Execute the catalog.add_data_tap stored procedure with the package execution_id.
• B. Execute the catalog.create_execution_dump stored procedure with the package execution_id.
• C. Run the DTEXEC utility with the /DumpOnError option.
• D. Run the DTEXEC utility with the /Reporting V optio

Answer: C