Top Quality 70-463 Free Practice Questions 2021

NEW QUESTION 1
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:
• Ensure that the deployment model supports changing the content of connection
strings by using parameters at execution time.
• Ensure that the deployment model automatically starts from calls to the catalog.start_execution stored procedure in the SSISDB database.
• Maximize performance at execution time.
• Minimize development effort.
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 parameter
• C. Ensure that the SSISDB database is created.
• D. Use a project deployment mode
• E. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package.
• F. Use a package deployment mode
• G. Use a SQL Server package configuration with a common filte
• H. Change the contents of the SSIS Configurations table at runtime.
• I. Use a package deployment mode
• J. Save each SSIS package to a file share that can be accessed from all environments.

Answer: A

NEW QUESTION 2
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 3
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 4
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 5
You are performance tuning a SQL Server Integration Services (SSIS) package to load sales data from a source system into a data warehouse that is hosted on Windows Azure SQL Database.
The package contains a data flow task that has seven source-to-destination execution trees. Only three of the source-to-destination execution trees are running in parallel.
You need to ensure that all the execution trees run in parallel. What should you do?

• A. Set the EngineThreads property of the data flow task to 7.
• B. Set the MaxConcurrentExcecutables property of the package to 7.
• C. Create seven data flow tasks that contain one source-to-destination execution tree each.
• D. Place the data flow task in a For Loop container that is configured to execute seven time

Answer: A

NEW QUESTION 6
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 7
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 8
You have a server named SQL1 that has SQL Server Integration Services (SSIS) installed. SQL1 has eight CPUs, 8 GB of RAM, RAID-10 storage, and a 10-gigabit Ethernet connection.
A package named Package1 runs on SQL1. Package1 contains 10 data tasks. Package1 queries 3 GB of data from the source system.
You review the performance statistics on SQL1 and discover that Package1 executes slower than expected.
You need to identify the problem that is causing Package1 to execute slowly. Which performance monitor counter data should you review?

• A. Processor% Idle Time
• B. Processor% User Time
• C. MemoryPages/sec
• D. SQLServer:SSIS Pipeline 10.0:Buffers in use

Answer: B

Explanation: The Processor: % User Time value “Corresponds to the percentage of time that the processor spends on executing user processes such as SQL Server.”
In other words, this is the percentage of processor non-idle time spent on user processes
If the Processor:% User Time high values are caused by SQL Server, the reason can be non-optimal SQL Server configuration, excessive querying, schema issues, etc. Further troubleshooting requires finding the process or batch that uses so much processor time
References:
https://www.sqlshack.com/sql-server-processor-performance-metrics-part-2-processor-user-timeprocessor- privilege-time-total-times-thread-metrics/

NEW QUESTION 9
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 2012. 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 configuration
• B. Select the registry entry at package execution time.
• C. For each target server, create separate Environments in the SSIS catalog of the host SQL Server SSIS instanc
• D. Select the appropriate Environment at package execution time.
• E. Create one SSIS catalog Environmen
• F. Change the values of each Environment variable at package execution time.
• G. For each target server, create a separate XML configuration fil
• H. Select the XML configuration file at package execution time.

Answer: B

NEW QUESTION 10
You are developing a project that contains multiple SQL Server Integration Services (SSIS) packages. The packages will be deployed to the SSIS catalog. One of the steps in each package accesses an FTP site to download data files.
You create project parameters to store the username and password that are used to access the FTP site.
You need to ensure that the username and password values are encrypted when they are deployed. What should you do?

• A. Convert the parameters to package parameters.
• B. Set the Sensitive property of the parameters to True.
• C. Set the ProtectionLevel property of the package to EncryptSensitiveWithPassword.
• D. Convert the project to the Legacy Deployment mode

Answer: B

NEW QUESTION 11
You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel workbook data into a Windows Azure SQL Database database. The package has been deployed to a production server that runs Windows Server 2008 R2 and SQL Server 2012.
The package fails when executed on the production server.
You need to ensure that the package can load the Excel workbook data without errors. You need to use the least amount of administrative effort to achieve this goal.
What should you do?

• A. Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option.
• B. Enable Address Windowing Extensions (AWE) for the local SQL Server instance.
• C. Replace the SSIS Excel source with a SSIS Flat File source.
• D. Install a 64-bit ACE driver and replace the Excel source with an OLE DB source.

Answer: D

Explanation: XLSX files, the new file type in Excel 2007/2010 cannot be opened with Excel Source/Destination in SSIS. To use XLSX in SSIS we need to install ACE driver and use OLE DB Source/Destination to read read/write data in XLSX files.
ACE is a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access (*.mdb and *.accdb) files and Microsoft Office Excel 7 (*.xls, *.xlsx, and *.xlsb) files to Microsoft SQL Server. If the SQL Server or your development box does not have ACE driver then install that 1st.
Note:
* The most efficient way to link to SQL Server is using an OLEDB connection manager. Incorrect:
Not B:
* Address Windowing Extensions (AWE) is a Microsoft Windows application programming interface that allows a 32-bit software application to access more physical memory than it has virtual address space.
* Address Windowing Extensions (AWE) is a set of extensions that allows an application to quickly manipulate physical memory greater than 4GB. Certain data-intensive applications, such as database management systems and scientific and engineering software, need access to very large caches of dat
A. In the case of very large data sets, restricting the cache to fit within an application's 2GB of user address space is a severe restriction.

NEW QUESTION 12
You develop a SQL Server Integration Services (SSIS) package in a project by using the Project Deployment Model. It is regularly executed within a multi-step SQL Server Agent job.
You make changes to the package that should improve performance.
You need to establish if there is a trend in the durations of the next 10 successful executions of the package. You need to use the least amount of administrative effort to achieve this goal.
What should you do?

• A. After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the package.
• B. Configure the package to send you an email upon completion that includes information about the duration of the packag
• C. After 10 executions, view the emails.
• D. Enable logging to the Application Event Log in the package control flow for the OnInformation even
• E. After 10 executions, view the Application Event Log.
• F. Enable logging to the Application Event Log in the package control flow for the OnPostExecute even
• G. After 10 executions, view the Application Event Log.

Answer: A

Explanation: The All Executions Report displays a summary of all Integration Services executions that have been performed on the server. There can be multiple executions of the sample package. Unlike the Integration Services Dashboard report, you can configure the All Executions report to show executions that have started during a range of dates. The dates can span multiple days, months, or years.
The report displays the following sections of information.
* Filter
Shows the current filter applied to the report, such as the Start time range.
* Execution Information
Shows the start time, end time, and duration for each package execution. You can view a list of the parameter values that were used with a package execution, such as values that were passed to a child package using the Execute Package task.

NEW QUESTION 13
DRAG DROP
You are the administrator for a Data Quality Server. You are adding a user who must have permission to:
Edit and execute a project
View the activity monitoring data
This user must not be able to:
Perform any kind of knowledge management
Create or change a knowledge base
Terminate an activity or perform administrative duties
You need to develop a Transact-SQL (T-SQL) script to meet these requirements.
What should you do? (To answer, drag the appropriate code segment or segments to the correct location or locations in the answer area.)

Answer:

Explanation:

NEW QUESTION 14
You are deploying a new SQL Server Integration Services (SSIS) package to five 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 and redeploying the package.
• The deployment procedure must be automated as much as possible.
• Performance must be maximized.
You need to set up a deployment strategy that meets the requirements. What should you do?

• A. Add an OnError event handler to the SSIS project.
• B. Use an msi file to deploy the package on the server.
• C. Open a command prompt and run the gacutil command.
• D. Open a command prompt and run the dtutil /copy command.
• E. Open a command prompt and run the dtexec /rep /conn command.
• F. Open a command prompt and run the dtexec /dumperror /conn command.
• G. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
• 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. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration inSQL Serve

Answer: D

NEW QUESTION 15
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package.
The package control flow will contain many tasks. The tasks will execute consecutively and none will execute more than once. Certain groups of tasks will share variable and transaction scope.
You need to group tasks together while enabling them to be collapsed and expanded as a group. Which item should you use from the SSIS Toolbox? (To answer, select the appropriate item in the answer area.)

Answer:

Explanation:

NEW QUESTION 16
You administer a Microsoft SQL Server 2012 database. The database contains a table that has the following definition:

You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard.
You need to ensure that the following requirements are met:
The first row of the file contains the first row of data.
Each record is of the same length. The date follows the U.S. date format.
The file supports international characters.
What should you do? (To answer, simply select the option or options in the answer area that you would configure.)

Answer:

Explanation: Each record is of the same length = Fixed Width. The file supports international characters = Unicode.
The date follows the U.S. date format = Locale (already configured).
The first row of the file contains the first row of data = Uncheck “Column names in the first data row”

NEW QUESTION 17
You are developing a SQL Server Integration Services (SSIS) package to load data into a Windows Azure SQL Database database. The package consists of several data flow tasks.
The package has the following auditing requirements:
If a data flow task fails, a Transact-SQL (T-SQL) script must be executed.
The T-SQL script must be executed only once per data flow task that fails, regardless of the nature of the error.
You need to ensure that auditing is configured to meet these requirements. 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.
• K. Deploy the project by using dtutil.exe with the /COPY DTS option.
• L. Deploy the project by using dtutil.exe with the /COPY SQL option.
• M. Deploy the .ispac file by using the Integration Services Deployment Wizard.
• N. Create a SQL Server Agent job to execute the SSISDB.catalog.va!idate_project stored procedure.
• O. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
• P. Create a SQL Server Agent job to execute theSSISDB.catalog.create_execution and SSISDB.catalog.start_execution stored procedures.
• Q. Create a table to store error informatio
• R. Create an error output on each data flow destination that writes OnTaskFailed event text to the table.

Answer: C

NEW QUESTION 18
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 (?)

Answer: D

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

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