Simulation 70-463 Free Practice Questions 2021

NEW QUESTION 1
You are using SQL Server Data Tools to develop a SQL Server Integration Services (SSIS) project. The first package that you create in this project contains a package connection that accesses a Microsoft Excel file. Additional packages in the project must also access this file.
You need to define and reuse the Excel file connection in all project packages. What should you do?

• A. Copy the package Connection Manager and paste it in the second package.
• B. Set the RetainSameConnection property of the package Connection Manager to True.
• C. Convert the package Connection Manager in the first package to a project Connection Manager.
• D. Convert the project to the Package Deployment mode

Answer: C

NEW QUESTION 2
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 10 source-to-destination execution trees.
Only five 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. Add OnError and OnWarning event handlers.
• B. Create a new project and add the package to the project.
• C. Query the ExecutionLog table.
• D. Add an Execute SQL task to the event handler

Answer: A

NEW QUESTION 3
You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse hosted on SQL Azure.
The package uses a Foreach container to process text files found in a folder. The package must be deployed to a single server by using the Project Deployment model.
Multiple SQL Server Agent jobs call the package. Each job is executed on a different schedule. Each job passes a different folder path to the package.
You need to configure the package to accept the folder path from each job. Which package configuration should you use?

• A. .dtsConfig file
• B. Registry Entry
• C. Environment Variable
• D. Parent Package Variable
• E. XML Configuration File

Answer: C

Explanation: Reference:
http://msdn.microsoft.com/en-us/library/ms345179.aspx http://msdn.microsoft.com/en-us/library/ms141708.aspx http://msdn.microsoft.com/en-us/library/hh213214.aspx http://msdn.microsoft.com/en-us/library/hh213296.aspx http://msdn.microsoft.com/en-us/library/hh213293.aspx

NEW QUESTION 4
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server
2012 database.
The package is allowed to connect to only one database. An Environment variable contains the name of the database.
The OLE DB project connection manager has been parameterized.
You need to configure the connection manager property to accept the value of the Environment variable.
Which property should you use? (To answer, configure the appropriate option or options in the dialog box in the answer area.)

Answer:

Explanation:

NEW QUESTION 5
You manage a SQL Server Master Data Services (MDS) environment.
A new application requires access to the product data that is available in the MDS repository. You need to design a solution that gives the application access to the product data with the least amount of development effort.
What should you do?

• A. Use sp_addlinkedserver to add a linked server to access the MDS database tables directly.
• B. Create a Subscription View in MDS.
• C. Use transactional replication for data synchronization.
• D. Create an OLE DB connection string that sets the Provider property to MD

Answer: B

Explanation: You create a subscription view to export Master Data Services data to a subscribing system such as SQL Server. You use the subscribing system to view the data in the Master Data Services database. References:
https://docs.microsoft.com/en-us/sql/master-data-services/overview-exporting-data-master-dataservices? view=sql-server-2021

NEW QUESTION 6
You are designing a data warehouse for a fresh food distribution business that stores sales by individual product. It stores sales targets by product category. Products are classified into subcategories and categories.
Each product is included in only a single product 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 product
• factProductTarget, used to record the monthly sales targets by product category
Reports must be developed against the warehouse that reports product sales by product, category and subcategory, and product sales targets.
You need to design the product dimension. The solution should use as few tables as possible while supporting all the requirements.
What should you do?

• A. Create two product tables, dimProduct and dimProductCategor
• B. ConnectfactSales to dimProduct and factProductTarget to dimProductCategory with foreign key constraint
• C. Direct the cube developer to use key granularity attributes.
• D. Create one product table, dimProduct, which contains product detail, category, and subcategory column
• E. Connect factSales to dimProduct with a foreign key constrain
• F. Direct the cube developer to use a non-key granularity attribute for factProductTarget.
• G. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategory, and a fourth bridge table that joins products to their appropriate category and subcategory table records with foreign key constraint
• H. Direct the cube developer to use key granularity attributes.
• I. Create three product tables, dimProduct, dimProductCategory, and dimProductSubcategor
• J. Connect factSales to all three product tables and connect factProductTarget to dimProductCategory with foreign key constraint
• K. Direct the cube developer to use key granularity attributes.

Answer: B

NEW QUESTION 7
DRAG DROP
You are designing a SQL Server Integration Services (SSIS) package to execute 12 Transact-SQL (T-SQL) statements on a SQL Azure database. The T-SQL statements may be executed in any order. The T-SQL statements have unpredictable execution times.
You have the following requirements:
• The package must maximize parallel processing of the T-SQL statements.
• After all the T-SQL statements have completed, a Send Mail task must notify administrators.
You need to design the SSIS package. 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 that imports data into a data warehouse.
You are developing the part of the SSIS package that populates the ProjectDates dimension table. The business key of the ProjectDates table is the ProjectName column.
The business user has given you the dimensional attribute behavior for each of the four columns in the ProjectDates table:
• ExpectedStartDate - New values should be tracked over time.
• ActualStartDate - New values should not be accepted.
• ExpectedEndDate - New values should replace existing values.
• ActualEndDate - New values should be tracked over time. You use the SSIS Slowly Changing Dimension Transformation.
You must configure the Change Type value for each source column.
Which Change Type values should you select? (To answer, drag the appropriate value from the list of values to the correct location or locations in the answer area.)

Answer:

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms141715.aspx http://msdn.microsoft.com/en-us/library/ms141662.aspx

NEW QUESTION 9
You are installing SQL Server Data Quality Services (DQS).
You need to give users belonging to a specific Active Directory group access to the Data Quality Server.
Which SQL Server application should you use?

• A. Data Quality Client with administrative credentials
• B. SQL Server Configuration Manager with local administrative credentials
• C. SQL Server Data Tools with local administrative permissions
• D. SQL Server Management Studio with administrative credentials

Answer: D

NEW QUESTION 10
HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package.
OnError and OnWarning events must be logged for viewing in the built-in SSIS reports by using SQL Server Management Studio.
You need to execute the package and minimize the number of event types that are logged. Which setting should you use? (To answer, change the appropriate setting in the answer area.)

Answer:

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

NEW QUESTION 11
You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table. You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and
then CustomerID.
Which Transact- SQL statement should you use?

• A. CREATE TABLE Customer (SourceID int NOT NULL IDENTITY,CustomerID int NOT NULL IDENTITY, CustomerName varchar(255) NOT NULL);
• B. CREATE TABLE Customer (SourceID int NOT NULL,CustomerID int NOT NULL PRIMARY KEY CLUSTERED,CustomerName varchar(255) NOT NULL);
• C. CREATE TABLE Customer(SourceID int NOT NULL PRIMARY KEY CLUSTERED,CustomerID int NOT NULL UNIQUE, CustomerName varchar(255) NOT NULL);
• D. CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL,CustomerName varchar(255) NOT NULL,CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED(SourceID, CustomerID));

Answer: D

NEW QUESTION 12
You are developing a SQL Server Integration Services (SSIS) package.
To process complex scientific data originating from a SQL Azure database, a custom task component is added to the project.
You need to ensure that the custom component is deployed on a test environment correctly. What should you do?

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

NEW QUESTION 13
In your SSIS solution, you need to load a large set of rows into the database as quickly as possible. The rows are stored in a delimited text file, and only one source column needs its data type converted from String (used by the source column) to Decimal (used by the destination column).
What control flow task would be most suitable for this operation?

• A. The File System task would be perfect in this case, because it can read data from files and can be configured to handle data type conversions.
• B. The Bulk Insert task would be the most appropriate, because it is the quickest and can handle data type conversions.
• C. The data flow task would have to be used, because the data needs to be transformed before it can be loaded into the table.
• D. No single control flow task can be used for this operation, because the data needs to be extracted from the source file, transformed, and then loaded into the destination table.At least three different tasks would have to be used--the Bulk Insert task to load the data into a staging database, a Data Conversion task to convert the data appropriately, and finally, an Execute SQL task to merge the transformed data with existing destination data.

Answer: C

Explanation: References:
https://docs.microsoft.com/en-us/sql/integration-services/control-flow/data-flow-task

NEW QUESTION 14
DRAG DROP
You have a data warehouse that contains all of the sales data for your company.
You plan to create a SQL Server Integration Services (SSIS) package to extract a list of files from a database and to load the files to the data warehouse.
You need to design a control flow for the SSIS package. The control flow must meet the following requirements:
-Load the data in a single transaction.
-Load multiple tables in parallel after extracting the data.
Which types of containers should you add to the design? To answer, drag the appropriate container types to the correct locations. Each container type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

Answer:

Explanation:

NEW QUESTION 15
You are designing a SQL Server Integration Services (SSIS) project that uses the Project Deployment Model.
The project will contain 10 packages. All the packages must connect to the same data source and destination.
You need to define and reuse the connection managers in all the packages by using the least development effort.
What should you do?

• A. Implement parent package variables.
• B. Copy and paste connection managers from one package to the others.
• C. Implement project connection managers.
• D. Implement package connection manager

Answer: C

NEW QUESTION 16
You have a database named Sales and a data warehouse named DataW.
From Sales, you plan to bulk insert to a table in DataW that is more than 2 TB. The process will insert a minimum of 11 million rows simultaneously.
You need to identify which data storage strategy must be used to minimize load times. Which data storage strategy should you identify?

• A. a nonclustered columnstore
• B. FileStream
• C. a durable In-Memory OLTP
• D. non-durable staging table

Answer: D

NEW QUESTION 17
HOTSPOT
You plan to design an SQL Server Integration Services (SSIS) package to move data from a source system to an SQL Server database named Warehouse.
You need to implement a data loading strategy for the package. The solution must ensure that if a failure occurs, the connection automatically resumes from the point of failure.
How should you configure each package property? To answer, select the appropriate options in the answer area.

Answer:

Explanation: Box 1 CheckPointUsage: Always
CheckpointUsage specifies whether checkpoints are used.
Always specifies that the checkpoint file is always used and that the package restarts from the point of the previous execution failure. If the checkpoint file is not found, the package fails.
Box 2: FailPackageOnFailure: False
You must set the FailPackageOnFailure property to true for all the containers in the package that you want to identify as restart points.
Box 3: SaveCheckPoints: Yes
SaveCheckpoints indicates whether the package saves checkpoints. This property must be set to True to restart a package from a point of failure.
Box 4: TransactionOption: Supported
TransactionOption in SSIS is used to start transactions, add executables to transactions or keep them out of transactions. It has 3 values; namely
-> Required :- The container which has the transactionoption set to this, joins any available transaction or else starts a new transaction .
-> Supported -> Joins any available transaction (started by its parent container) but if not available, does not start one.
-> Not Supported -> Does not support transactions. The transactions follow all the
References:
https://docs.microsoft.com/en-us/sql/integration-services/packages/restart-packages-by-usingcheckpoints? view=sql-server-2021

NEW QUESTION 18
You are creating a Data Quality Services (DQS) solution. You must provide statistics on the accuracy of the data.
You need to use DQS profiling to obtain the required statistics. Which DQS activity should you use?

• A. Cleansing
• B. Matching
• C. Knowledge Discovery
• D. Matching Policy

Answer: A

Explanation: Ref: http://msdn.microsoft.com/en-gb/library/hh213055(v=sql.110).aspx

NEW QUESTION 19
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. Script Component
• B. Audit
• C. Destination Assistant
• D. Data Conversion

Answer: A

Explanation: The Script component can be used as a source, a transformation, or a destination. This component supports one input and multiple outputs. Depending on how the component is used, it supports either an input or outputs or both. The script is invoked by every row in the input or output.
If used as a source, the Script component supports multiple outputs.
If used as a transformation, the Script component supports one input and multiple outputs. If used as a destination, the Script component supports one input.