Microsoft Implementing a Data Warehouse with Microsoft SQL Server 2012/2014 - 070-463 Exam Practice Test
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?
You need that the business applications can access curated and validated data from the databases.
Which features should you deploy to the server?
Correct Answer: D
You deploy a server that has SQL Server installed.
You deploy a SQL Server Integration Services (SSIS) package to the server.
You need to automate the execution of the package. The solution must ensure that you receive a notification if the package to execute.
In which order should you perform all the actions?
You deploy a SQL Server Integration Services (SSIS) package to the server.
You need to automate the execution of the package. The solution must ensure that you receive a notification if the package to execute.
In which order should you perform all the actions?
Correct Answer:
Explanation
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.)
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.)
Correct 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
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?
You need to complete the post-installation configuration.
What should you do?
Correct Answer: A
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
You are developing a SQL Server Integration Services (SSIS) package.
The package contains several tasks that must repeat until an expression evaluates to FALSE.
You need to add and configure a container to enable this design.
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,)
The package contains several tasks that must repeat until an expression evaluates to FALSE.
You need to add and configure a container to enable this design.
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,)
Correct Answer:
Explanation
Box 1: Open the Control Flow designer of the package.
Box 2: Add and edit a For Loop container.
Box 3: Configure the EvalExpression property.
Note:
* You create the control flow in a package by using the control flow designer.
Integration Services includes three types of containers that you can use in a control flow.
/ Foreach Loop container
/ For Loop container
/ Sequence container
* For Loop Container
The For Loop container defines a repeating control flow in a package. The loop implementation is similar to the For looping structure in programming languages. In each repeat of the loop, the For Loop container evaluates an expression and repeats its workflow until the expression evaluates to False.
The For Loop container uses the following elements to define the loop:
/ An optional initialization expression that assigns values to the loop counters.
/ An evaluation expression that contains the expression used to test whether the loop should stop or continue.
/ An optional iteration expression that increments or decrements the loop counter.
You are developing a SQL Server Integration Services (SSIS) project to read and write data from a Windows Azure SQL Database database to a server that runs SQL Server 2012.
The connection will be used by data flow tasks in multiple SSIS packages. The address of the target Windows Azure SQL Database database will be provided by a project parameter.
You need to create a solution to meet the requirements by using the least amount of administrative effort.
What should you do?
The connection will be used by data flow tasks in multiple SSIS packages. The address of the target Windows Azure SQL Database database will be provided by a project parameter.
You need to create a solution to meet the requirements by using the least amount of administrative effort.
What should you do?
Correct Answer: A
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
You are developing a SQL Server Integration Services (SSIS) package. The data source for the data flow task is a table that has been configured as a change data capture (CDC) table. You are using a CDC Source component to obtain the CDC data.
The CDC Source component has the following requirements:
* The output must include metadata columns that indicate which source columns have changed.
* The output must return only one change row per source row that is modified in the current CDC processing range.
You need to configure the CDC Source component.
Which CDC processing mode should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.)
The CDC Source component has the following requirements:
* The output must include metadata columns that indicate which source columns have changed.
* The output must return only one change row per source row that is modified in the current CDC processing range.
You need to configure the CDC Source component.
Which CDC processing mode should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.)
Correct Answer:
Explanation
Ref: http://msdn.microsoft.com/en-gb/library/hh231004.aspx
You are creating a Data Quality Services (DQS) solution. You must provide statistics on the accuracy of the data.
You need to use DQS profiling to obtain the required statistics.
Which DQS activity should you use?
You need to use DQS profiling to obtain the required statistics.
Which DQS activity should you use?
Correct Answer: B
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
You plan to design an SQL Server Integration Services (SSIS) package to move data from a source system to an SQL Server database named Warehouse.
You need to implement a data loading strategy for the package. The solution must ensure that if a failure occurs, the connection automatically resumes from the point of failure.
How should you configure each package property? To answer, select the appropriate options in the answer area.
You need to implement a data loading strategy for the package. The solution must ensure that if a failure occurs, the connection automatically resumes from the point of failure.
How should you configure each package property? To answer, select the appropriate options in the answer area.
Correct Answer:
Explanation
Box 1 CheckPointUsage: Always
CheckpointUsage specifies whether checkpoints are used.
Always specifies that the checkpoint file is always used and that the package restarts from the point of the previous execution failure. If the checkpoint file is not found, the package fails.
Box 2: FailPackageOnFailure: False
You must set the FailPackageOnFailure property to true for all the containers in the package that you want to identify as restart points.
Box 3: SaveCheckPoints: Yes
SaveCheckpoints indicates whether the package saves checkpoints. This property must be set to True to restart a package from a point of failure.
Box 4: TransactionOption: Supported
TransactionOption in SSIS is used to start transactions, add executables to transactions or keep them out of transactions. It has 3 values; namely
-> Required :- The container which has the transactionoption set to this, joins any available transaction or else starts a new transaction .
-> Supported -> Joins any available transaction (started by its parent container) but if not available, does not start one.
-> Not Supported -> Does not support transactions.
The transactions follow all the
References:
https://docs.microsoft.com/en-us/sql/integration-services/packages/restart-packages-by-using-checkpoints?view
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.)
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.)
Correct Answer:
Explanation
Box 1:
Box 2:
Box 3:
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.
You have a SQL Server Integration Services (SSIS) package. The package contains a script task that has the following comment.
// Update DataLoadBeginDate variable to the beginning of yesterday
The script has the following code.
Dts.variables["User::DataLoadBeginDate''].Value = DataTime.Today.AddDays(-1); The script task is configured as shown in the exhibit. (Click the Exhibit button.)
When you attempt to execute the package, the package fails and returns the following error message: ''Error:
Exception has been thrown by the target of an invocation.''
You need to execute the package successfully.
What should you do?
// Update DataLoadBeginDate variable to the beginning of yesterday
The script has the following code.
Dts.variables["User::DataLoadBeginDate''].Value = DataTime.Today.AddDays(-1); The script task is configured as shown in the exhibit. (Click the Exhibit button.)
When you attempt to execute the package, the package fails and returns the following error message: ''Error:
Exception has been thrown by the target of an invocation.''
You need to execute the package successfully.
What should you do?
Correct Answer: B
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
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 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?
Correct Answer: D
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?
You need to ensure that the database requirements are met.
What should you install?
Correct Answer: C
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
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.)
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.)
Correct Answer:
Explanation
