Actual 70-463 Exam Questions and Answers 2021

NEW QUESTION 1
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 2
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 3
In your ETL process, there are three external processes that need to be executed in sequence, but you do not want to stop execution if any of them fails.
Can this be achieved by using precedence constraints? If so, which precedence constraints can be used?

• A. No, this cannot be achieved just by using precedence constraints.
• B. Yes, this can be achieved by using completion precedence constraints between the first and the second and between the second and the third Execute Process tasks, and by using a success precedence constraint between the third Execute Process task and the following task.
• C. Yes, this can be achieved by using completion precedence constraints between the first and the second, between the second and the third, and between the third Execute Process task and the following task.
• D. Yes, this can be achieved by using failure precedence constraints between the first and the second, and between the second and the third Execute Process tasks, and by using a completion precedence constraint between the third Execute Process task and the following task.

Answer: B

Explanation: References:
https://msdn.microsoft.com/en-us/library/cc505848.aspx

NEW QUESTION 4
To support the implementation of new reports, Active Directory data will be downloaded to a SQL Server database by using a SQL Server Integration Services (SSIS) 2012 package.
The following requirements must be met:
All the user information for a given Active Directory group must be downloaded to a SQL Server table.
The download process must traverse the Active Directory hierarchy recursively.
You need to configure the package to meet the requirements by using the least development effort. Which item should you use?

• A. Script task
• B. Script component configured as a transformation
• C. Script component configured as a source
• D. Script component configured as a destination

Answer: C

NEW QUESTION 5
Your company has a data mart that contains customer review data. The customer reviews are of the product review or service review type. You plan to load each customer review type to a separate fact table.
You create a SQL Server Integration Services (SSIS) package as shown in the exhibit. (Click the Exhibit button.)

You need to modify the package to load the customer review data to the new fact tables. The SSIS package
must contain a single data flow. What should you do?

• A. Set the transformation to Conditional Split.
• B. Set the transformation to CDC Splitter.
• C. Add a second transformation and set the transformation to Merge.
• D. Add a second transformation and set the transformation to Multicas

Answer: A

NEW QUESTION 6
You are the administrator for a Master Data Services (MDS) environment.
A user who is not an administrator must be able to access Master Data Manager. You need to assign permissions to the user.
Which function should you use?

• A. Explorer
• B. System Administration
• C. Version Management
• D. User and Group Permissions
• E. Integration Management

Answer: D

Explanation: In the User and Group Permissions functional area, administrators can grant permission to functional areas, to attributes (on the Models tab), and to members (on the Hierarchy Members tab). Overlapping permissions are resolved to determine a user’s permission to each individual attribute. References:
https://docs.microsoft.com/en-us/sql/master-data-services/user-and-group-permissions-functionalarea- master-data-manager?view=sql-server-2021

NEW QUESTION 7
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 CDC Source component has the following requirements:
The output must include metadata columns that indicate which source columns have changed. The output must return only one change row per source row that is modified in the current CDC processing range.
You need to configure the CDC Source component.
Which CDC processing mode should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

Answer:

Explanation: Ref: http://msdn.microsoft.com/en-gb/library/hh231004.aspx

NEW QUESTION 8
DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that loads data into a data
warehouse hosted on Windows Azure SQL Database.
You must combine two data sources together by using the ProductID column to provide complete details for each record. The data retrieved from each data source is sorted in ascending order by the ProductID column.
You need to develop a data flow that imports the data while meeting the requirements.
How should you develop the data flow? (To answer, drag the appropriate transformation from the list of transformations to the correct location in the answer area.)

Answer:

Explanation:

NEW QUESTION 9
You are administering SQL Server Integration Services (SSIS) permissions on a production server that runs SQL Server 2012.
Package developers in your company must have permission to perform the following tasks only on their own projects:
View projects and packages, View Environments, Validate packages, Execute packages. You need to grant rights to the developers without assigning unnecessary privileges.
What should you do? (Each correct answer presents part of a solution. Choose all that apply.)

• A. Add developer logins to the db_ssisltduser role in the msdb database.
• B. Add developer logins to the db_ssisoperator role in the msdb database.
• C. Grant Execute permission in the projects for the developer logins.
• D. Grant Read permission in the SSIS catalog folder, the projects, and the Environments.
• E. Add developer logins to the ssis_admin role in the SSISDB database.
• F. Grant Modify permission in the projects for the developer logins.

Answer: BD

Explanation: B: db_ssisoperator
* Read actions Enumerate all packages. View all packages. Execute all packages. Export all packages.
Execute all packages in SQL Server Agent.
* Write actions None
D: Need read permissions on the Environments in order to be able to view them. Incorrect:
Not A: db_ssisltduser
* Read actions Enumerate own packages. Enumerate all packages. View own packages. Execute own packages. Export own packages.
* Write Actions Import packages. Delete own packages.
Change own package roles. Not E: db_ssisadmin
Too many permissions (such as delete all packages).

NEW QUESTION 10
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 Data Profiling transform.
• B. Create a custom .NET application based on the Knowledgebase class.
• C. Create a data quality project.
• D. Create a CLR stored procedure based on the Knowledgebase class.
• E. Create a Master Data Services (MDS) business rule.

Answer: C

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

NEW QUESTION 11
You are developing a SQL Server Integration Services (SSIS) project to read and write data from a Windows Azure SQL Database database to a server that runs SQL Server 2012.
The connection will be used by data flow tasks in multiple SSIS packages. The address of the target Windows Azure SQL Database database will be provided by a project parameter.
You need to create a solution to meet the requirements by using the least amount of administrative effort.
What should you do?

• A. Add a SQLMOBILE connection manager to each package.
• B. Add an ADO.NET project connection manager.
• C. Add a SQLMOBILE project connection manager.
• D. Add an ADO.NET connection manager to each data flow task.
• E. Add a SQLMOBILE connection manager to each data flow task.
• F. Add an ADO.NET connection manager to each package.

Answer: B

Explanation: Ref: http://www.databasejournal.com/features/mssql/windows-azure-sql-database-uploading-databy- using-sql-server-integration-services.html

NEW QUESTION 12
You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS).
The package includes custom scripts that must be upgraded. You need to upgrade the package to SQL Server 2012. Which tool should you use?

• A. SSIS Upgrade Wizard in SQL Server 2008 BIDS
• B. SSIS Upgrade Wizard in SQL Server Data Tools
• C. SQL Server DTExecUI utility (dtexecui.exe)
• D. SQL Server dtexec utility (dtexec.exe)

Answer: B

NEW QUESTION 13
You are designing a SQL Server Integration Services (SSIS) package that will load data from a source system to a data mart.
You need to combine conditionally the value of two project parameters to a package variable. What should you do?

• A. Configure an event handler.
• B. Set the data type property of the variable.
• C. Set the Expression property of the variable.
• D. Modify the namespace of the variable.

Answer: C

Explanation: The Expression property of a variable specifies the expression that is assigned to the variable. References:
https://docs.microsoft.com/en-us/sql/integration-services/integration-services-ssis-variables

NEW QUESTION 14
You are developing a SQL Server Integration Services (SSIS) package.
The package is stored as the myPackage.dtsx file in the root directory of the C drive of the SSIS server. You need to run the package from the command prompt.
Which command should you use?

• A. dtexec /sql myPackage
• B. dtexec /d "C:File SystemmyPackage.dtsx"
• C. dtexec /f "C:myPackage.dtsx"
• D. dtexec /com "myPackage.dtsx"

Answer: C

NEW QUESTION 15
You are completing the installation of the Data Quality Server component of SQL Server Data Quality
Services (DQS).
You need to complete the post-installation configuration. What should you do?

• A. Run the DQSInstaller.exe command.
• B. Install the data providers that are used for data refresh.
• C. Install ADOMD.NET.
• D. Run the dbimpexp.exe command.

Answer: A

Explanation: References:
http://msdn.microsoft.com/en-us/library/ff877917.aspx http://msdn.microsoft.com/en-us/library/gg492277.aspx

NEW QUESTION 16
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. Data Cleansing transformation
• C. Fuzzy Lookup transformation
• D. Term Lookup transformation
• E. Data Profiling task

Answer: A

NEW QUESTION 17
You are designing a data warehouse for a software distribution business that stores sales by software title. It stores sales targets by software category. Software titles are classified into subcategories and
categories. Each software title is included in only a single software subcategory, and each subcategory is included in only a single category. The data warehouse will be a data source for an Analysis Services cube.
The data warehouse contains two fact tables: factSales, used to record daily sales by software title
factTarget, used to record the monthly sales targets by software category
Reports must be developed against the warehouse that reports sales by software title, category and subcategory, and sales targets.
You need to design the software title dimension. The solution should use as few tables as possible while supporting all the requirements.
What should you do?

• A. Create three software tables, dimSoftware, dimSoftwareCategory, and dimSoftwareSubcategory and a fourth bridge table that joins software titles to their appropriate category and subcategory table records with foreign key constraint
• B. Direct the cube developer to use key granularity attributes.
• C. Create three software tables, dimSoftware, dimSoftwareCategory, and dimSoftwareSubcategor
• D. Connect factSales to all three tables and connect factTarget to dimSoftwareCategory with foreign key constraint
• E. Direct the cube developer to use key granularity attributes.
• F. Create one table, dimSoftware, which contains Software Detail, Category, and Subcategory column
• G. Connect factSales to dimSoftware with a foreign key constrain
• H. Direct the cube developer to use a non-key granularity attribute for factTarget.
• I. Create two tables, dimSoftware and dimSoftwareCategor
• J. Connect factSales to dimSoftware and factTarget to dimSoftwareCategory with foreign key constraint
• K. Direct the cube developer to use key granularity attributes.

Answer: C

NEW QUESTION 18
You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads data from a source system that uses the SQL Server change data capture (CDC) feature.
You have added a CDC Source component to the data flow to read changed data from the source system.
You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations.
Which data flow transformation should you use?

• A. Audit
• B. DQS Cleansing
• C. CDC Splitter
• D. Pivot

Answer: C

Explanation: The CDC splitter splits a single flow of change rows from a CDC source data flow into different data flows for Insert, Update and Delete operations
Ref: http://msdn.microsoft.com/en-us/library/hh758656.aspx

NEW QUESTION 19
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

Answer: C