70-463 Exam
70 463 pdf : May 2021 Edition
Q71. You are developing a data flow transformation to merge two data sources. One source contains product data and the other source contains data about the country in which the product was manufactured. Both data sources contain a two-character CountryCode column and both use SQL Server. Both data sources contain an ORDER BY clause to sort the data by the CountryCode column in ascending order.
You use a Merge Join transformation to join the data.
You need to ensure that the Merge Join transformation works correctly without additional transformations. What should you do? (Each answer presents a part of the solution. Choose all that apply.)
A. set the appropriate SortKeyPosition properties on the data sources.
B. set the ValidateExternalMetaData property on the Merge Join transformation to True.
C. set the IsSorted property on both data sources.
D. Set the MaxBuffersPerlnput property on the Merge Join transformation to 2.
Answer: A,C
Explanation: References: http://msdn.microsoft.com/en-us/library/ms141775.aspx
http://msdn.microsoft.com/en-us/library/ms137653.aspx http://siddhumehta.blogspot.com/2009/05/validateexternalmetadata-property.html http://msdn.microsoft.com/en-us/library/ms135950.aspx
Q72. You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS). The package includes custom scripts that must be upgraded.
You need to upgrade the package to SQL Server 2012.
Which tool should you use?
A. SQL Server dtexec utility (dtexec.exe)
B. SQL Server DTExecUI utility (dtexecui.exe)
C. SSIS Upgrade Wizard in SQL Server Data Tools
D. SQL Server Integration Services Deployment Wizard
Answer: C
Explanation: Use the SSIS Package Upgrade Wizard to upgrade SQL Server 2005 Integration Services (SSIS) packages and SQL Server 2008 Integration Services (SSIS) packages to the package format for the current (2012) release of SQL Server Integration Services.
Q73. 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:
Q74. You are designing a SQL Server Integration Services (SSIS) 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. OnPostExecute
B. OnWarning
C. OnExecStatusChanged
D. OnVariableValueChanged
Answer: A
Reference: http://msdn.microsoft.com/en-us/library/ms140223.aspx
Q75. 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
Q76. 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
Q77. You are creating a SQL Server Integration Services (SSIS) package to populate a fact table from a source table. The fact table and source table are located in a SQL Azure database. The source table has a price field and a tax field. The OLE DB source uses the data access mode of Table.
You have the following requirements:
. The fact table must populate a column named TotalCost that computes the sum of the price and tax columns. . Before the sum is calculated, any records that have a price of zero must be discarded.
You need to create the SSIS package in SQL Server Data Tools.
In what sequence should you order four of the listed components for the data flow task? (To answer, move the appropriate components from the list of components to the answer area and arrange them in the correct order.)
Answer:
Q78. 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 Log.
Answer: A
Q79. You are developing a SQL Server Integration Services (SSIS) package.
The package is stored as the myPackage.dtsx file in the root directory of the C drive of the
SSIS server.
You need to run the package from the command prompt.
Which command should you use?
A. dtexec /sql myPackage
B. dtexec /d "C:\File System\myPackage.dtsx"
C. dtexec /f "C:\myPackage.dtsx"
D. dtexec /com "myPackage.dtsx"
Answer: C
Q80. You are designing a data warehouse that uses SQL Server 2012.
The data warehouse contains a table named factSales that stores product sales. The table has a clustered index on the primary key, four foreign keys to dimension tables, and an aggregate column for sales totals. All key columns use the int data type and the aggregate column uses the money data type.
You need to increase the speed of data retrieval from the factSales table.
Which index type should you add to the table?
A. Clustered
B. Semantic search
C. Nonclustered
D. XML
Answer: C