Microsoft 70-463 Braindumps 2021

NEW QUESTION 1
DRAG DROP
A new SQL Server Integration Services (SSIS) project is deployed to the SSIS catalog.
To troubleshoot some data issues, you must output the data streaming through several data flows into text files for further analysis. You have the list of data flow package paths and identification strings of the various task components that must be analyzed.
You need to create these output files with the least amount of administrative and development effort.
Which three stored procedures should you execute 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-gb/library/hh230989(v=sql.110).aspx

NEW QUESTION 2
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:

NEW QUESTION 3
DRAG DROP
You administer a Microsoft SQL Server database. You want to import data from a text file to the database.
You need to ensure that the following requirements are met: Data import is performed by using a stored procedure.
Data is loaded as a unit and is minimally logged.
Which data import command and recovery model should you choose? (To answer, drag the
appropriate data import command or recovery model to the appropriate location or locations in the answer area. Each data import command or recovery model 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: References:
http://msdn.microsoft.com/en-us/library/ms162802.aspx http://msdn.microsoft.com/en-us/library/ms188365.aspx http://msdn.microsoft.com/en-us/library/ms175937.aspx http://msdn.microsoft.com/en-us/library/aa337544.aspx

NEW QUESTION 4
You are developing a SQL Server Integration Services (SSIS) package.
The package sources data from an HTML web page that lists product stock levels.
You need to implement a data flow task that reads the product stock levels from the HTML web page. Which data flow source should you use?

• A. Raw File source
• B. XML source
• C. Custom source component
• D. Flat File source

Answer: C

NEW QUESTION 5
A SQL Server Integration Services (SSIS) package was deployed two weeks ago with the Project Deployment Model.
Sometimes the package is started as part of a multistep SQL job. At other times, the package is started manually by a database administrator by using the Object Explorer in SQL Server Management Studio.
You need to identify the authenticated user responsible for starting the package each time it executes.
How can you find this information?

• A. In the SSISDB.[catalog], query the .[executions] view.
• B. In the SSISDB.[catalog] , query the [event_messages] view.
• C. In SQL Server Management Studio, view the SQL Agent Job History.
• D. In SQL Server Management Studio, view the SQL Agent Error Log.
• E. In SQL Server Management Studio, view the SQL Server Lo

Answer: A

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

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

Answer: B

NEW QUESTION 9
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 10
You are developing a SQL Server Integration Services (SSIS) package.
You need to design a package to change a variable value during package execution by using the least amount of development effort.
What should you use?

• A. Expression task
• B. Script task
• C. Execute SQL task
• D. Execute Process task
• E. Term Extraction transformation

Answer: A

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

NEW QUESTION 11
You are the data steward for a Business Intelligence project.
You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.
You need to produce the CSV file with the least amount of development effort. What should you do?

• A. Create an Integration Services package and use a Fuzzy Lookup transform.
• B. Create a Master Data Services (MDS) model.
• C. Create a Data Quality Project.
• D. Create an Integration Services package and use a DQS Cleansing transform.
• E. Create an Integration Services package and use a Fuzzy Grouping transform.

Answer: C

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

NEW QUESTION 12
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 13
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:

NEW QUESTION 14
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. Enable logging to an XML file in the package control flow for the Onlnformation even
• B. After 10 executions, view the XML file.
• C. After 10 executions, view the job history for the SQL Server Agent job.
• D. After 10 executions, in SQL Server Management Studio, view the Execution Performance subsection of the All Executions report for the package.
• E. Enable logging to an XML file in the package control flow for the OnPostExecute even
• F. After 10 executions, view the XML file.

Answer: C

NEW QUESTION 15
You are implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure database into a data warehouse.
The source system contains redundant or inconsistent dat

• A. When the package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a text file for further analysis.You need to establish the best technique to log these invalid rows while keeping the amount of development effort to a minimum.What should you do?
• B. Add an OnError event handler to the SSIS project.
• C. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
• D. Use an msi file to deploy the package on the server.
• E. Open a command prompt and run the gacutil command.
• 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 dtutil /copy command.
• 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. Open a command prompt and run the dtexec /rep /conn command.
• L. Open a command prompt and run the dtexec /dumperror /conn comman

Answer: I

Explanation: References:
http://technet.microsoft.com/en-us/library/hh230989.aspx
http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/ms162820.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx http://technet.microsoft.com/en-us/library/ms140223.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx

NEW QUESTION 16
You are deploying a project to the SQL Server Integration Services (SSIS) catalog.
You need to ensure that the project is deployed to the production environment with the least amount of administrative effort.
What should you do?

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

Answer: G

Explanation: Reference:
http://msdn.microsoft.com/en-us/library/hh479588.aspx http://msdn.microsoft.com/en-us/library/hh213290.aspx http://msdn.microsoft.com/en-us/library/hh213373.aspx

NEW QUESTION 17
You are designing a data warehouse hosted on Windows Azure SQL Database. The data warehouse currently includes the dimUser and dimRegion dimension tables and the factSales fact table. The dimUser table contains records for each user permitted to run reports against the warehouse, and the dimRegion table contains information about sales regions.
The system is accessed by users from certain regions, as well as by area supervisors and users from the corporate headquarters.
You need to design a table structure to ensure that certain users can see sales data for only certain regions. Some users must be permitted to see sales data from multiple regions.
What should you do?

• A. For each region, create a view of the factSales table that includes a WHERE clause for the region.
• B. Create a userRegion table that contains primary key columns from the dimUser and dimRegion tables.
• C. Add a region column to the dimUser table.
• D. Partition the factSales table on the region colum

Answer: B

NEW QUESTION 18
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. Run the Data Quality Server Installer.
• B. Install the data providers that are used for data refresh.
• C. Run the dbimpexp.exe command.
• D. Install the Analysis Services OLE DB Provide

Answer: A

NEW QUESTION 19
You are reviewing the design of a student dimension table in an existing data warehouse hosted on SQL Azure.
The current dimension design does not allow the retention of historical changes to student attributes such as ParentOccupation.
You need to redesign the dimension to enable the full historical reporting of changes to multiple student attributes including ParentOccupation.
What should you do?

• A. Add CurrentValue and PreviousValue columns to the student dimension.
• B. Enable Snapshot Isolation on the data warehouse.
• C. Add an IsCurrent column to the student dimension.
• D. Add StartDate and EndDate columns to the student dimension.

Answer: D

Explanation: Adding a start and end date will give you this ability as when a record is inserted and given a start and end date, you’ll have the ability to determine when they were active therefore giving you a retention of historical changes

NEW QUESTION 20
DRAG DROP
You have a SQL Server Integration Services (SSIS) package that runs on a server named SQL1 and connects to a SQL Server database on a server named SQL2.
You schedule the package to run via a SQL Server Agent job.
The package fails and generates the following error message: “Unknown server or Access is Denied”. You discover that the package is configured to authenticate by using Windows authentication and that the SQL Server Agent on SQL1 uses NT ServiceSQLSERVERAGENT as a service account. You verify that other jobs run on the server without errors.
You create a user account named CONTOSOJobProcess1 and provide all of the required access to the tables on SQL2.
You need to ensure that the package runs successfully via the SQL Server Agent job.
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: