Microsoft 70-463 Practice Exam
2022.png "Implementing a Data Warehouse with Microsoft SQL Server 2012 v13.0 (70-463) 2022")
You are deploying a new SQL Server Integration Services (SSIS) project to the test environment.
A package in the project uses a custom task component.
You need to ensure that the custom object is deployed on the test environment correctly.
What should you do?
- A. Run the package by using the dtexec /rep /conn command.
- B. Create a reusable custom logging component.
- C. Create an OnError event handler.
- D. Use the gacutil command.
- E. Use the dtutil /copy command.
- F. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
- G. Run the package by using the dtexec /dumperror /conn command.
- H. Use the Project Deployment Wizard.
- I. Deploy the package by using an msi file.
- J. Add a data tap on the output of a component in the package data flow. K. Run the package by using the dtexecui.exe utility and the SQL Log provider.
Answer : D
Reference:
http://msdn.microsoft.com/en-us/library/ms403356.aspx
You are building a SQL Server Integration Services (SSIS) package to load product data sourced from a SQL Azure database to a data warehouse. Before the product data is loaded, you create a batch record by using an Execute SQL task named Create Batch.
After successfully loading the product data, you use another Execute SQL task named Set
Batch Success to mark the batch as successful.
You need to create and execute an Execute SQL task to mark the batch as failed if either the Create Batch or Load Products task fails.
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:
References:
http://msdn.microsoft.com/en-us/library/ms141003.aspx
http://msdn.microsoft.com/en-us/library/ms141261.aspx
http://msdn.microsoft.com/en-us/library/ms141722.aspx
http://msdn.microsoft.com/en-us/library/ms139895.aspx
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 :
Explanation:
Box 1:
Box 2:
Box 3:
Ref: http://msdn.microsoft.com/en-us/library/hh479592.aspx
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 correct answer presents part of the solution. Choose all that apply.)
- A. Change the ORDER BY clause on the product source to order by ProductName.
- B. Change the Merge Join transformation to a Merge transformation.
- C. Set the appropriate SortKeyPosition properties on the data sources.
- D. Set the IsSorted property on both data sources.
Answer : C,D
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- environments-environment-variables-in-ssis-in-denali.aspx
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
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
You are developing a SQL Server Integration Services (SSIS) package to load data into a
- A. OLE DB Destination with Fast Load
- B. SQL Server Destination
- C. ADO NET Destination without Bulk Insert
- D. ADO NET Destination with Bulk Insert
- E. OLE DB Destination without Fast Load
Answer : A
Reference: http://msdn.microsoft.com/en-us/library/ms141237.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms139821.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms141095.aspx
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.NET.
Answer : C
Explanation:
References:
http://msdn.microsoft.com/en-us/library/ff877917.aspx
http://msdn.microsoft.com/en-us/library/gg492277.aspx
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. Convert the package Connection Manager in the first package to 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
A SQL Server Integration Services (SSIS) package on a computer is failing periodically in production. The package was created less than one year ago and was deployed to the
SSIS catalog.
Sometimes the package is started on a SQL Agent schedule; sometimes the package is started manually by an SSIS developer 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 failed in the past.
Where can you find this information?
A. the SQL Server Log -
B. the SSISDB.[catalog].[executions] view
C. the SSISDB.[catalog].[event_messages] view
D. the SQL Agent Job History -
E. the SQL Agent Error Log -
Answer : B Topic 2, Volume B
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 :
You are editing a SQL Server Integration Services (SSIS) project named Project1 in SQL
Server Data Tools.
A package Connection Manager has been parameterized with project scope.
You need to display the parameters that have been generated for the Connection Manager.
What should you use? To answer, select the appropriate setting or settings in the answer area.
Answer :
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. Join the two fact tables.
- B. Merge the fact tables.
- C. Create a time dimension that can join to both fact tables at their respective granularity.
- D. Create a surrogate key for the time dimension.
Answer : C
Explanation: References:
http://msdn.microsoft.com/en-us/library/ms174537.aspx
http://technet.microsoft.com/en-us/library/ms174832.aspx
http://msdn.microsoft.com/en-us/library/ms174884.aspx
http://decipherinfosys.wordpress.com/2007/02/01/surrogate-keys-vs-natural-keys-for- primary-key/ http://www.agiledata.org/essays/keys.html http://www.databasejournal.com/features/mssql/article.php/3922066/SQL-Server-Natural-
Key-Verses-Surrogate-Key.htm -
http://www.jamesserra.com/archive/2012/01/surrogate-keys/
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 :
You are validating whether a SQL Server Integration Services (SSIS) package named
Master.dtsx in the SSIS catalog is executing correctly.
You need to display the number of rows in each buffer passed between each data flow component of the 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:
1. Run with verbose settings
2. Get Execution ID from .executions
3. Get stats from .execution_data_statistics ( rows_sent )
Ref: http://msdn.microsoft.com/en-us/library/hh230986.aspx
Read more
