Tested 70-463 Dumps Questions 2021

NEW QUESTION 1
You are designing a SQL Server Integration Services (SS1S) package that uploads a file to a table named Orders in a SQL Azure database.
The company's auditing policies have the following requirements:
• An entry must be written to a dedicated SQL Server log table named OrderLog.
• The entry must be written as soon as the file upload task completes.
You need to meet the company's policy requirements. Which event handler should you use?

• A. OnProgress
• B. Onlnformation
• C. OnPostExecute
• D. OnComplete

Answer: C

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

NEW QUESTION 2
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. Natural
• B. Explicit
• C. Parent
• D. Recursive

Answer: D

Explanation: Reference: http://technet.microsoft.com/en-us/library/ff487006.aspx Reference: http://msdn.microsoft.com/en-us/library/ee633747.aspx Reference: http://technet.microsoft.com/en-us/library/ee633759.aspx

NEW QUESTION 3
DRAG DROP
A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse.
The SSIS catalog includes two Environments:
Test
Production
Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the test or production data warehouses. You need to execute deployed packages by using either of the defined Environments.
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: We need to add references to the Test and Production environments to the project. Then we can map the variables in the project to the environment variables defined in the environments.
When you execute a package in a project that references multiple environments (Test and Production in this case), we can select which environment the package runs under.

NEW QUESTION 4
You need to extract data from a table in a SQL Server 2012 database. What connection manager types can you use? (Choose all that apply.)

• A. An ODBC connection manager
• B. An OLE DB connection manager
• C. A File connection manager
• D. An ADO.NET connection manager

Answer: ABD

Explanation: References:
https://technet.microsoft.com/en-us/library/ms141013(v=sql.110).aspx https://docs.microsoft.com/en-us/sql/integration-services/data-flow/extract-data-by-using-theodbc- source
https://msdn.microsoft.com/en-us/library/ms254937(v=vs.100).aspx

NEW QUESTION 5
If you want to switch content from a nonpartitioned table to a partition of a partitioned table, what conditions must the nonpartitioned table meet? (Choose all that apply.)

• A. It must have the same constraints as the partitioned table.
• B. It must have the same compression as the partitioned table.
• C. It must be in a special PartitionedTables schema.
• D. It must have a check constraint on the partitioning column that guarantees that all of the data goes toexactly one partition of the partitioned table.
• E. It must have the same indexes as the partitioned table.

Answer: ABDE

Explanation: References:
https://technet.microsoft.com/en-us/library/ms191160(v=sql.105).aspx

NEW QUESTION 6
Occasionally a job that executes an existing SQL Server Integration Services (SSIS) package does not complete and nothing is processed.
You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts.
What should you do?

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

Answer: J

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms140246.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx

NEW QUESTION 7
You develop a SQL Server Integration Services (SSIS) package that imports Windows Azure SQL Database data into a data warehouse every night.
The Windows Azure SQL Database data contains many misspellings and variations of abbreviations. To import the data, a developer used the Fuzzy Lookup transformation to choose the closestmatching string from a reference table of allowed values. The number of rows in the reference table
is very large.
If no acceptable match is found, the Fuzzy Lookup transformation passes a null value. The current setting for the Fuzzy Lookup similarity threshold is 0.50.
Many values are incorrectly matched.
You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading performance.
What should you do?

• A. Change the similarity threshold to 0.40.
• B. Decrease the maximum number of matches per lookup.
• C. Change the similarity threshold to 0.85.
• D. Increase the maximum number of matches per lookup.

Answer: C

Explanation: * Similarity threshold
Set the similarity threshold at the component level by using the slider. The closer the value is to 1, the closer the resemblance of the lookup value to the source value must be to qualify as a match. Increasing the threshold can improve the speed of matching since fewer candidate records need to be considered.

NEW QUESTION 8
DRAG DROP
A new dedicated server is used to execute resource-intensive SQL Server Integration Services (SSIS) 2012 packages. The environment that you are deploying the packages to has the following constraints:
The operating system is Windows Server 2008 R2. The SSIS packages are stored in the SSIS catalog.
Some of these SSIS packages use 32-bit custom components.
You need to install only the components that are required to deploy and run the packages on the new server.
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: Note: Step 1:
Step 2:
If you require 32-bit versions of the Integration Services runtime and tools to run certain packages in 32-bit mode, you must also install SQL Server Data Tools (SSDT).
Note:
For a complete installation of Integration Services, together with the tools and documentation for developing and managing packages, select both Integration Services and the following Shared Features:
* SQL Server Data Tools to install the tools for designing packages.
* Management Tools - Complete to install SQL Server Management Studio for managing packages.
* Client Tools SDK to install managed assemblies for Integration Services programming.
Step 3: How to Modify the Service Configuration File to Connect to a Named Instance of SQL Server To connect to a named instance of SQL Server, you must modify the service configuration file, MsDtsSrvr.ini.xml.

NEW QUESTION 9
You plan to deploy a package to a server that has SQL Server installed. The server contains a SQL Server Integration Services (SSIS) catalog.
You need to ensure that the package credentials are encrypted. Which protection level should you set for the package?

• A. ServerStorage
• B. EncryptAllWithUserKey
• C. EncryptSensitivewithPassword
• D. EncryptSensitivewithUserKey

Answer: D

Explanation: The SSISDB catalog uses the ServerStorage protection level. When you deploy an Integration Services project to the Integration Services server, the catalog automatically encrypts the package data and sensitive values. The catalog also automatically decrypts the data when you retrieve it.
If you export the project (.ispac file) from the Integration Services server to the file system, the system automatically changes the protection level to EncryptSensitiveWithUserKey. References:
https://docs.microsoft.com/en-us/sql/integration-services/security/access-control-for-sensitivedata- in-packages?view=sql-server-2021

NEW QUESTION 10
You are creating a SQL Server Master Data Services (MDS) model. This model is used to store a master list of products.
An attribute must be added to the Product entity to define the sales manager responsible for each product.
You need to create an attribute in the Product entity that prevents users from entering invalid sales manager values.
Which type of attribute should you create?

• A. Recursive
• B. Explicit
• C. Domain-based
• D. User-defined
• E. Derived
• F. Parent

Answer: C

Explanation: References:
http://msdn.microsoft.com/en-us/library/bb190163.aspx http://msdn.microsoft.com/en-us/library/ee633737.aspx http://msdn.microsoft.com/en-us/library/ee633759.aspx http://msdn.microsoft.com/en-us/library/ee633745.aspx http://msdn.microsoft.com/en-us/library/ee633724.aspx http://msdn.microsoft.com/en-us/library/ee633733.aspx http://msdn.microsoft.com/en-us/library/ff487058.aspx

NEW QUESTION 11
Your company has several line-of-business applications. The applications use a server that has SQL Server installed and contains several databases.
You need that the business applications can access curated and validated data from the databases. Which features should you deploy to the server?

• A. Data Quality Services (DQS)
• B. SQL Server Analysis Services (SSAS) dimension hierarchies.
• C. SQL Server Integration Services (SSIS)
• D. Master Data Services (MDS) subscription

Answer: C

NEW QUESTION 12
DRAG DROP
A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse.
The SSIS catalog includes two Environments:
✑ Development
✑ QA
Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the development or QA data warehouses. You need to be able to execute deployed packages by using either of the defined Environments.
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 13
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. DQS Cleansing
• B. Merge Join
• C. Pivot
• D. Conditional Split

Answer: D

NEW QUESTION 14
DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that downloads data from a Windows Azure SQL Database database.
A stored procedure will be called in an Execute SQL task by using an ODBC connection. This stored procedure has only the @CustomerID parameter of type INT.
A project parameter named CustID will be mapped to the stored procedure parameter
@CustomerID.
You need to ensure that the value of the CustID parameter is passed to the @CustomerID stored procedure parameter.
In the Parameter Mapping tab of the Execute SQL task editor, how should you configure the parameter? (To answer, drag the appropriate option or options to the correct location or locations in the answer area.)

Answer:

Explanation:

NEW QUESTION 15
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 flat file. Additional packages in the project must also access this file.
You need to define and reuse the flat file connection in all project packages. What should you do?

• A. move a table named OLDCUSTOMERS a project Connection Manager.
• B. Copy the package Connection Manager and paste it into the second package.
• C. Convert the project to the Package Deployment model.
• D. Set the ProtectionLevel property of the package Connection Manager to DontSaveSensitive to reuse the flat file connection.

Answer: A

NEW QUESTION 16
You are installing the Data Quality Server component of Data Quality Services.
You need to provision the hardware and install the software for the server that runs the Data Quality Server.
You must ensure that the minimum Data Quality Server prerequisites are met. What should you do?

• A. Install Microsoft Internet Explorer 6.0 SP1 or later.
• B. Install SQL Server 2012 Database Engine.
• C. Make sure the server has at least 4 GB of RAM.
• D. Install Microsoft SharePoint Server 2010 Standard Edition.

Answer: B

Explanation: Data Quality Server Minimum System Requirements
* SQL Server 2012 Database Engine.
* Memory (RAM): Minimum: 2 GB Recommended: 4 GB or more
Note: SQL Server Data Quality Services (DQS) is a new feature in SQL Server 2012 that contains the following two components: Data Quality Server and Data Quality Client.

NEW QUESTION 17
HOTSPOT
You are developing a data flow to load sales data into a fact table. In the data flow, you configure a Lookup Transformation in full cache mode to look up the product data for the sale.
The lookup source for the product data is contained in two tables.
You need to set the data source for the lookup to be a query that combines the two tables. Which page of the Lookup Transformation Editor should you select to configure the query? To answer, select the appropriate page in the answer area.

Answer:

Explanation: References:
http://msdn.microsoft.com/en-us/library/ms141821.aspx http://msdn.microsoft.com/en-us/library/ms189697.aspx

NEW QUESTION 18
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 settings should you select? (To answer, select the appropriate setting or settings in the answer area. Each Change Type may be used once, more than once, or not at all.)

Answer:

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

NEW QUESTION 19
You are designing a data warehouse that contains a product dimension.
The product dimension contains the original product attributes as well as the most recent set of updated attributes. When a product is updated, the original product attributes must be preserved. The product dimension design must:
Support a Type 3 Slowly Changing Dimension (SCD) process Minimize the total storage consumed
You need to design a solution that meets these requirements. What should you do?

• A. Design two table
• B. One table will hold the original product surrogate key and attribute
• C. The other table will hold the most recent product surrogate key and attributes.
• D. Design a table as a Type 2 SCD dimension that holds the surrogate key for each product and its attribute
• E. Limit the number of records per product within the table to two.
• F. Design a table to hold the surrogate key for each product and its attributes as well as additional columns to store the original attributes.
• G. Design a table to hold the surrogate key for each product and its attribute
• H. Add a column to store an indicator that identifies the record as original data or recent data.

Answer: C