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

You install a SQL Server 2012 database engine instance on a production server. A month later, you install SQL Server 2012 Integration Services (SSIS).
You must develop an SSIS project and deploy it to the server by using the Project Deployment model.
Operations Log records that are outside the configured retention period must be cleaned automatically.
You need to create the SSIS catalog on the production server.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

NO: 213
Which of the following T-SQL functions is not very useful for capturing lineage information?

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?

You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD).
You need to add a task or component to the package that allows you to implement the SCD logic.
What should you use?

NO: 204
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.
What should you do?

You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named ProductCode.
Some of the data to be loaded will reference products that need special processing logic in the data flow.
You need to enable separate processing streams for a subset of rows based on the source product code.
Which data flow transformation should you use?

You are performance tuning a SQL Server Integration Services (SSIS) package to load sales data from a source system into a Microsotf Azure SQL Data Warehouse instance. The package contains a data flow task that has 10 source-to-destination execution trees.
Only five of the source-to-destination execution trees are running in parallel.
You need to ensure that all the execution trees run in parallel.

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.

You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model.
The project is deployed to a single SSIS catalog, and transfers data to and from multiple databases hosted on SQL Server.
The project must be configured to be able to export data to and from five different production servers that run SQL Server 2012. Each target server requires different values for connection strings and parameters in the SSIS project.
You need to meet the requirements by using the least amount of administrative effort.
What should you do?

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 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?

You are developing a SQL Server Integration Services (SSIS) package to insert new data into a data mart. The package uses a Lookup transformation to find matches between the source and destination.
The data flow has the following requirements:
*New rows must be inserted.
*Lookup failures must be written to a flat file.
In the Lookup transformation, the setting for rows with no matching entries is set to Redirect rows to no match output. You need to configure the package to direct data into the correct destinations.
How should you design the data flow outputs? (To answer, drag the appropriate transformation from the list of answer options to the correct location in the answer area.)

A SQL Server Integration Services (SSIS) 2012 package currently downloads sales data from a Windows Azure SQL Database database.
To improve sales data accuracy, exchange rates must be downloaded daily from a public HTTP website instead of from a weekly flat file. The public website hosts a comma-separated values (CSV) file that contains one row per currency.
You need to download the CSV file to the environment.
What should you use to retrieve the document from the website?

You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel workbook data into a Windows Azure SQL Database database. The package has been deployed to a production server that runs Windows Server 2008 R2 and SQL Server 2012.
The package fails when executed on the production server.
You need to ensure that the package can load the Excel workbook data without errors. You need to use the least amount of administrative effort to achieve this goal.
What should you do?