Tested 70-463 Dumps 2021

NEW QUESTION 1
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 ($Project::StringVar)
• C. INSERT INTO dbo.Table (variablevalue) VALUES (?)
• D. INSERT INTO dbo.Table (variablevalue) VALUES ($Package::StringVar)

Answer: C

NEW QUESTION 2
DRAG DROP
You are editing a SQL Server Integration Services (SSIS) package that contains a task with a sensitive property.
You need to create a project parameter and configure it so that its value is encrypted when it is deployed to the SSIS catalog.
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:

NEW QUESTION 3
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package.
You use a Data Profiling task to examine the data from a source system. You need to establish: The minimum and maximum dates for the datetime columns in the source data
The minimum, maximum, and average values for numeric columns in the source data
You need to use the appropriate profile type in the Data Profiling task.
Which profile type should you use? (To answer, select the appropriate profile type in the answer area.)

Answer:

Explanation:

NEW QUESTION 4
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package. The package contains a userdefined variable named @Queue which has an initial value of 10.
The package control flow contains many tasks that must repeat execution until the @Queue variable equals 0.
You need to enable the tasks to be grouped together for repeat execution.
Which item should you add to the package? (To answer, select the appropriate item in the answer area.)

Answer:

Explanation:

NEW QUESTION 5
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 6
DRAG DROP
You are building a SQL Server Integration Services (SSIS) package to load product data sourced from a SQL Azure database to a data warehouse. Before the product data is loaded, you create a batch
record by using an Execute SQL task named Create Batch. After successfully loading the product data, you use another Execute SQL task named Set Batch Success to mark the batch as successful.

You need to create and execute an Execute SQL task to mark the batch as failed if either the Create Batch or Load Products task fails.
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/ms141003.aspx http://msdn.microsoft.com/en-us/library/ms141261.aspx http://msdn.microsoft.com/en-us/library/ms141722.aspx http://msdn.microsoft.com/en-us/library/ms139895.aspx

NEW QUESTION 7
DRAG DROP
You are creating a sales data warehouse. When a product exists in the product dimension, you update the product name. When a product does not exist, you insert a new record.
In the current implementation, the DimProduct table must be scanned twice, once for the insert and again for the update. As a result, inserts and updates to the DimProduct table take longer than expected.
You need to create a solution that uses a single command to perform an update and an insert.
How should you use a MERGE T-SQL statement to accomplish this goal? (To answer, drag the appropriate answer choice from the list of options to the correct location or locations in the answer area. You may need to drag the split bar between panes or scroll to view content.)

Answer:

Explanation: References:
http://msdn.microsoft.com/en-us/library/bb510625.aspx http://msdn.microsoft.com/en-us/library/cc280522.aspx

NEW QUESTION 8
You have a SQL Server Integration Services (SSIS) package. The package contains a script task that has the following comment.
// Update DataLoadBeginDate variable to the beginning of yesterday The script has the following code.
Dts.variables[“User::DataLoadBeginDate’’].Value = DataTime.Today.AddDays(-1); The script task is configured as shown in the exhibit. (Click the Exhibit button.)

When you attempt to execute the package, the package fails and returns the following error
message: ‘’Error: Exception has been thrown by the target of an invocation.’’ You need to execute the package successfully.
What should you do?

• A. Add the dataLoadBegin Date variable to the ReadOnlyVariables property.
• B. Add the DataLoadBeginDate variable to the ReadWriteVariables property.
• C. Modify the entry point of the script.
• D. Change the scope of the DataLoadBeginDate variable to Packag

Answer: B

Explanation: You add existing variables to the ReadOnlyVariables and ReadWriteVariables lists in the Script Task Editor to make them available to the custom script. Within the script, you access variables of both types through the Variables property of the Dts object.
References:
https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/task/usingvariables- in-the-script-task?view=sql-server-2021

NEW QUESTION 9
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package.
The package must run a parameterized query against a Windows Azure SQL Database database. You need to use the least amount of development effort to meet the package requirement. Which task should you use? (To answer, select the appropriate task in the answer area.)

Answer:

Explanation: Running Parameterized SQL Commands
SQL statements and stored procedures frequently use input parameters, output parameters, and return codes. The Execute SQL task supports the Input, Output, and ReturnValue parameter types. You use the Input type for input parameters, Output for output parameters, and ReturnValue for return codes.
Ref: http://msdn.microsoft.com/en-us/library/ms141003.aspx
In SSIS there are two tasks than can be used to execute SQL statements: Execute T-SQL Statement and Execute SQL. What is the difference between the two?
The Execute T-SQL Statement task tasks less memory, parse time, and CPU time than the Execute SQL task, but is not as filexible. If you need to run parameterized queries, save the query results to
variables, or use property expressions, you should use the Execute SQL task instead of the Execute TSQL Statement task.
Ref: http://www.sqlservercentral.com/blogs/jamesserra/2012/11/08/ssis-execute-sql-task-vsexecute- t-sql-statement-task/

NEW QUESTION 10
You are using the Knowledge Discovery feature of the Data Quality Services (DQS) client application to modify an existing knowledge base.
In the mapping configuration, two of the three columns are mapped to existing domains in the knowledge base. The third column, named Group, does not yet have a domain.
You need to complete the mapping of the Group column. What should you do?

• A. Map a composite domain to the source column.
• B. Create a composite domain that includes the Group column.
• C. Add a domain for the Group column.
• D. Add a column mapping for the Group column.

Answer: C

NEW QUESTION 11
DRAG DROP
A SQL Server Integration Services (SSIS) package named DataFeed interacts with an external vendor data feed. The package is executed several times a day, either as part of other packages' control flow or by itself. The external data feed is unreliable because network failures and slow response times are frequent. The package is currently deployed on the file system.
To analyze the reliability of the external data feed, you must collect execution data. Every time the DataFeed package is executed, the following information must be logged:
✑ Start Time
✑ End Time
✑ Execution Result
✑ Execution Duration
You need to design a logging solution that meets the requirements by using the least amount of administrative and development effort.
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: Ref: http://msdn.microsoft.com/en-us/library/hh479592.aspx

NEW QUESTION 12
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 13
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 14
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.
Force packages to load all settings in the configuration. Support Encrypting File System (EFS) formats.
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 15
DRAG DROP
You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment model.
The project contains many packages. It is deployed on a server named SQLTest1. The project will be deployed to several servers that run SQL Server 2012.
The project accepts one required parameter. The data type of the parameter is a string.
A SQL Agent job is created that will call the Loading.dtsx package in the project. A job step is created for the SSIS package.
The job must pass the value of an SSIS Environment Variable to the project parameter. The value of the Environment Variable must be configured differently on each server that runs SQL Server. The value of the Environment Variable must provide the server name to the project parameter.
You need to configure SSIS on the SQLTest1 server to pass the Environment Variable to the package. Which four actions should you perform in sequence by using SQL Server Management Studio?
(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 16
You are installing the Data Quality Client on user desktops.
You need to ensure that the prerequisite software components are installed.
Which components must be present to meet this goal? (Each correct answer presents part of the solution. Choose all that apply.)

• A. SQL Server Management Studio
• B. Internet Explorer 6.0 SP1 or later
• C. Microsoft Silverlight 5
• D. .NET Framework 3.5 SP1
• E. .NET Framework 4.0
• F. Microsoft Silverlight 4
• G. SQL Server Data Tools

Answer: BE

Explanation: Before installing DQS, make sure that your computer meets the minimum system requirements. Data Quality Client requirement:
.NET Framework 4.0 (installed during the Data Quality Client installation, if not already installed) Internet Explorer 6.0 SP1 or later

NEW QUESTION 17
DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that imports unsorted data into a data warehouse hosted on SQL Azure.
You have the following requirements:
• A destination table must contain all of the data in two source tables.
• Duplicate records must be inserted into the destination table.
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: References:
http://msdn.microsoft.com/en-us/library/ms141703.aspx http://msdn.microsoft.com/en-us/library/ms141775.aspx http://msdn.microsoft.com/en-us/library/ms141020.aspx http://msdn.microsoft.com/en-us/library/ms140182.aspx

NEW QUESTION 18
DRAG DROP
You deploy a server that has SQL Server installed.
You deploy a SQL Server Integration Services (SSIS) package to the server.
You need to automate the execution of the package. The solution must ensure that you receive a notification if the package to execute.
In which order should you perform all the actions?

Answer:

Explanation:

NEW QUESTION 19
DRAG DROP
You use SQL Server Integration Services (SSIS) for extract, transformation, load (ETL) processing. Issues concerning addresses are discovered in the data warehouse that you manage. Validation must separate the addresses into three categories:
Valid addresses Autocorrected addresses Invalid addresses
You need to enhance the SSIS packages to perform address validation by using an external service. Which transformation should you use? (To answer, drag the appropriate transformation from the list of options to the correct location or locations in the answer area.)

Answer:

Explanation: