70-463 | Microsoft 70-463 Dumps 2021

NEW QUESTION 1
DRAG DROP
You develop 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 Development1. 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 master.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 Development1 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: References:
http://msdn.microsoft.com/en-us/library/hh479588.aspx http://msdn.microsoft.com/en-us/library/hh213230.aspx http://msdn.microsoft.com/en-us/library/hh213214.aspx
http://sqlblog.com/blogs/jamie_thomson/archive/2010/11/13/ssis-server-catalogs-environmentsenvironment- variables-in-ssis-in-denali.aspx

NEW QUESTION 2
You are designing a SQL Server Integration Services (SSIS) 2012 package that imports data from a Windows Azure SQL Database database into a SQL Server database.
The SSIS package has the following requirements:
Every night, a very large amount of data is imported into the staging database. Package processing time must be minimized.
The package must run on its own dedicated server when it is deployed to production. Transaction log activity when data is imported must be minimized.
You need to design the package to meet the requirements. Which destination component should you use?

• A. DataReader
• B. OLE DB
• C. ADO.NET
• D. SQL Server

Answer: B

NEW QUESTION 3
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 4
You are preparing to install SQL Server 2012 Master Data Services (MDS). You need to ensure that the database requirements are met.
What should you install?

• A. Microsoft SharePoint Foundation 2010 SP1
• B. SQL Server 2012 Enterprise (64-bit) x64 on the database server
• C. SQL Server 2012 Data Center (64-bit) x64 on the database server
• D. SQL Server 2008 Enterprise (64-bit) x64 on the database server

Answer: B

Explanation: * Master Data Services is a new feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2012.
* SQL Server 2012 Enterprise features include Master Data Services:

Note:
* Microsoft SQL Server Master Data Services is a Master Data Management (MDM) product from Microsoft, which will ship as a part of the Microsoft SQL Server database.Originally code-named Bulldog, Master Data Services is the rebranding of the Stratature MDM product titled +EDM, which Microsoft acquired in June 2007. Master Data Services is architecturally similar to +EDM, with increased integration with other Microsoft applications as well as some new features. Master Data Services first shipped with Microsoft SQL Server 2008 R2.

NEW QUESTION 5
You are installing SQL Server Data Quality Services (DQS).
You need to give specific users access to the Data Quality Server. Which SQL Server application should you use?

• A. SQL Server Configuration Manager
• B. SQL Server Data Tools
• C. SQL Server Management Studio
• D. Data Quality Client

Answer: C

Explanation: Ref: http://msdn.microsoft.com/en-us/library/hh213045.aspx

NEW QUESTION 6
To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a logging methodology is put in place.
The methodology has the following requirements:
• The deployment process must be simplified.
• All the logs must be centralized in SQL Server.
• Log data must be available via reports or T-SQL.
• Log archival must be automated.
You need to configure a logging methodology that meets the requirements while minimizing the amount of deployment and development effort.
What should you do?

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

NEW QUESTION 7
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 8
You are reviewing the design of an existing fact table named factSales, which is loaded incrementally from a SQL Azure database by a SQL Server Integration Services (SSIS) package each hour. The fact table has approximately 4 billion rows and is dimensioned by product, sales date, and sales time of day.

The database administrator is concerned about the rapid growth of the database and users experience poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses this fact table reports sales by product name, sale month, and sale year. No other reports will be created against this table.
You need to reduce the report processing time and minimize the growth of the database. What should you do?

• A. Create an indexed view over the fact table to sum orderTotal by month.
• B. Create a view over the fact table to sum orderTotal by month.
• C. Change the granularity of the fact table to month.
• D. Partition the fact table by productKe

Answer: C

NEW QUESTION 9
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 10
You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model. All packages in the project must log custom messages.
You need to produce reports that combine the custom log messages with the system-generated log messages. 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.validate_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 OnError event text to the table.
• S. Create a table to store error informatio
• T. Create an error output on each data flow destination that writes OnTaskFailed event text to the table.

Answer: G

NEW QUESTION 11
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 12
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. Depending on the value in a source column, the package redirects rows to one of five different data flow paths.
You need to add a data flow transformation to support the package redirection. Which data flow transformation should you use?

• A. Conditional Split
• B. Pivot
• C. Multicast
• D. Lookup

Answer: A

NEW QUESTION 13
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 14
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. Install the Analysis Services OLE DB Provider.
• B. Make the data available for DQS operations.
• C. Run the Data Quality Server Installer.
• D. Install ADOMD.NE

Answer: C

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

NEW QUESTION 15
You have a secured database that contains all of the customer data for your company. You plan to use a project deployment model.
You need to create a SQL Server Integration Services (SSIS) package that connects to the database by using a dedicated username and password. The solution must ensure that the password used for the connection string is encrypted.
Which two actions should you perform? Each correct answer presents part of the solution.

• A. Modify the set_execution_parameter_value stored procedure.
• B. Set the package protection level to EncryptSensitiveWithPassword.
• C. Select the Sensitive check box for the catalog environment.
• D. Set the sensitive property of the package parameter to True.
• E. Set the package protection level to EncryptSensitiveWithUserKe

Answer: BD

NEW QUESTION 16
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 time mapping table.
• B. Change the level of granularity in both fact tables to be the same.
• C. Merge the fact tables.
• D. Create a view on the sales tabl

Answer: B

NEW QUESTION 17
You develop a SQL Server Integration Services (SSIS) package that imports SQL Azure data into a data warehouse every night.
The SQL Azure data contains many misspellings and variations of abbreviations. To import the data, a developer used the Fuzzy Lookup transformation to choose the closest-matching 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. Decrease the maximum number of matches per lookup.
• B. Change the similarity threshold to 0.55.
• C. Change the Exhaustive property to True.
• D. Increase the maximum number of matches per lookup.

Answer: B

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

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

Answer: D

Explanation: Script Component Transformation:
The transformation that uses script to extract, transform, or load data. Note:
* SQL Server Integration Services provides three different types of data flow components: sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases. Transformations modify, summarize, and clean dat
A. Destinations load data into data stores or create in-memory datasets. Incorrect:
Not A, Not B: No Data Transforms called Source Assistant or Destination Assistant Not C: Not related to auditing