Microsoft Implementing a Data Warehouse with Microsoft SQL Server 2012/2014 - 070-463 Exam Practice Test

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.)
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
NO: 215
Which of the following statements about simple and complex data movements are true? (Choose all that apply.)
Correct Answer: C,D
Explanation: (Only visible to ExamsLabs members)
You have a data warehouse that contains all of the sales data for your company. The data warehouse contains several SQL Server Integration Services (SSIS) packages.
You need to create a custom report that contains the total number of rows processed in the package and the time required for each package to execute.
Which view should you include in the report?
Correct Answer: A
Explanation: (Only visible to ExamsLabs members)
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.)
Correct Answer: A,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.)
Correct Answer:

Explanation

References:
http://msdn.microsoft.com/en-us/library/hh4
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 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?
Correct Answer: J
NO: 216
Which of the following statements are true? (Choose all that apply.)
Correct Answer: A,B
Explanation: (Only visible to ExamsLabs members)
You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different sources. One source is hosted in a SQL Azure database. Each source contains products for different distributors.
Products for each distributor source must be combined for insertion into a single product table destination.
You need to select the appropriate data flow transformation to meet this requirement.
Which transformation types should you use? (Each correct answer presents a complete solution. Choose all that apply.)
Correct Answer: A,E
Explanation: (Only visible to ExamsLabs members)
You are administering SQL Server Integration Services (SSIS) permissions on a production server that runs SQL Server 2012. All packages are deployed to the SSIS catalog.
Package developers in your company must have permission to perform the following tasks only on their own projects:
*View projects and package
*View Environments
*Validate packages
*Execute packages
You need to grant rights to the developers without assigning unnecessary privileges.
What should you do? (Each correct answer presents part of a solution. Choose all that apply.)
Correct Answer: B,F
Explanation: (Only visible to ExamsLabs members)
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?
Correct Answer: A
Explanation: (Only visible to ExamsLabs members)
You are designing a SQL Server Integration Services (SSIS) package. The package moves order-related data to a staging table named Order. Every night the staging data is truncated and then all the recent orders from the online store database are inserted into the staging table.
Your package must meet the following requirements:
*If the truncate operation fails, the package execution must stop and report an error.
*If the Data Flow task that moves the data to the staging table fails, the entire refresh operation must be rolled back.
*For auditing purposes, a log entry must be entered in a SQL log table after each execution of the Data Flow task.
The TransactionOption property for the package is set to Required.
You need to design the package to meet the requirements.
How should you design the control flow for the package? (To answer, drag the appropriate setting from the list of settings to the correct location or locations in the answer area.)
Correct Answer:

Explanation

References:
http://msdn.microsoft.com/en-us/library/ms137690.aspx
http://msdn.microsoft.com/en-us/library/ms141144.aspx
You administer a Microsoft SQL Server 2012 database. The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)


Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you use an identity specification to include a seed of 1,000 and an increment of 1?
Correct Answer: C
You are the administrator for a Master Data Services (MDS) environment.
A user who is not an administrator must be able to access Master Data Manager.
You need to assign permissions to the user.
Which function should you use?
Correct Answer: D
Explanation: (Only visible to ExamsLabs members)
Your company is evaluating the data cleansing capabilities of SQL Server Data Quality Services (DQS).
A stand-alone server will be used to host DQS, as well as all related services necessary to run this service.
You need to install the DQS components on the server by using the minimum amount of steps possible. You also need to grant permissions to a login named DataStewards to enable DataStewards to edit and execute DQS projects.
Which four 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 2:

Box 3:

Box 1:

Box 4:

Note:
Step 1-2:
* SQL Server Data Quality Services (DQS) is a new feature in SQL Server 2012 that contains the following two components: Data Quality Server and Data Quality Client.
* You have to use the SQL Server 2012 Setup to install DQS components. When you run the SQL Server Setup, you have to go through a series of installation wizard pages to select appropriate options based on your requirements.
/ Feature Selection
Data Quality Services under Database Engine Services to install the Data Quality Server.
Data Quality Client to install Data Quality Client.
Step 3-4:
* The Data Quality Services (DQS) security infrastructure is based upon the SQL Server security infrastructure. A database administrator grants a user a set of permissions by associating the user with a DQS role. Doing so determines the DQS resources that the user has access to and the functional activities that the user is allowed to perform.
* There are four roles for DQS. One is the database administrator (DBA) who deals primarily with product installation, database maintenance, and user management. This role primarily uses the SQL Server Management Studio, rather than within the Data Quality Client application. Their server role is sysadmin.
The three other roles are information workers, data stewards who use the product directly by working in the Data Quality Client application. These roles include the following:
/ The DQS Administrator (dqs_administrator role) can do everything in the scope of the product.
/ The DQS KB Editor (dqs_kb_editor role) can perform all of the DQS activities, except for administration.
/ The DQS KB Operator (dqs_kb_operator role) can edit and execute a project. They cannot perform any kind of knowledge management; they cannot create or change a knowledge base. They can see the activity monitoring data, but cannot terminate an activity or perform administrative duties.
Ref: http://msdn.microsoft.com/en-gb
You have a SQL Server data warehouse that contains all of the customer data for your company.
You plan to deploy an archiving solution.
You need to move a table named OLDCUSTOMERS to a new storage device. OLDCUSTOMERS contains a clustered index.
Which four 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.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.
Correct Answer:

Explanation
NO: 214
Which statements best describe SQL Server Development Tools (SSDT)? (Choose all that apply.)
Correct Answer: D
NO: 209
A part of your data consolidation process involves extracting data from Excel workbooks.
Occasionally, the data contains errors that cannot be corrected automatically.
How can you handle this problem by using SSIS?
Correct Answer: C
Explanation: (Only visible to ExamsLabs members)
You are designing a SQL Server Integration Services (SSIS) project that uses the Project Deployment Model.
The project will contain 10 packages. All the packages must connect to the same data source and destination.
You need to define and reuse the connection managers in all the packages by using the least development effort.
What should you do?
Correct Answer: A
You are writing a SQL Server Integration Services (SSIS) package that transfers data from a legacy system.
Data integrity in the legacy system is very poor. Invalid rows are discarded by the package but must be logged to a CSV file for auditing purposes.
You need to establish the best technique to log these invalid rows while minimizing the amount of development effort.
What should you do?
Correct Answer: G
Explanation: (Only visible to ExamsLabs members)
NO: 206
Some of the data your company processes is sent in from partners via email.
How would you configure an SMTP connection manager to extract files from email messages?
Correct Answer: C
Explanation: (Only visible to ExamsLabs members)