Implementing a Data Warehouse with Microsoft SQL Server 2012
Question No: 91 – (Topic 2)
You are using a SQL Server Integration Services (SSIS) project that is stored in the SSIS catalog. An Environment has been defined in the SSIS catalog.
You need to add the Environment to the project. Which stored procedure should you use?
Answer: B Explanation:
Environments (Test, Production etc) are associated with projects by creating references to the environments in the projects.
Question No: 92 – (Topic 2)
You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart.
You have the following requirements:
鈥nsure that the process supports the creation of an exception report that details possible duplicate key values, null ratios within columns, and column-length distributions of values.
鈥nsure that users can generate the exception report in an XML format.
鈥se the minimum development effort.
You need to design the SSIS solution to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Use a Data Profiling task. Use a Data Flow task to extract the XML output of the Data Profiling task into a SQL Server table. Query the table to view the exceptions.
Use Data Flow tasks to process the clean data.
Use a Data Profiling task. Read the exceptions in Data Profile Viewer.
Design a stored procedure that examines data for common dirty data patterns. Use an Execute SQL task.
Question No: 93 DRAG DROP – (Topic 2)
You administer a Microsoft SQL Server database. Service accounts for SQL Agents are configured to use a local user. A Microsoft SQL Server Integration Services (SSIS) job step has been created within a SQL Server Agent job. The SSIS package accesses a network share when exporting data from a SQL Server database.
When you execute the SQL Server Agent job, it fails due to a permissions failure on a share on a remote server. You need to ensure that the SQL Server Agent job can execute the SSIS package. 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.)
Question No: 94 CORRECT TEXT – (Topic 2)
You are designing a package control flow. The package moves sales order data from a SQL Azure transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being added to the transactional database.
The current design of the package control flow is shown in the answer area. (Click the Exhibit button.)
The Insert New Orders Data Flow task must meet the following requirements:
->Usage of the tempdb database should not be impacted.
->Concurrency should be maximized, while only reading committed transactions.
->If the task fails, only that task needs to be rolled back.
You need to configure the Insert New Orders Data Flow task to meet the requirements.
How should you configure the transaction properties? To answer, select the appropriate setting or settings in the answer area.
Answer: IsolationLevel = ReadCommited. TransactionOption = Required
Question No: 95 – (Topic 2)
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?
Parent Package Variable
XML Configuration File
Question No: 96 – (Topic 2)
You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed.
You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables.
You need to configure the SQL Server environment to support these packages. What should you do?
Create SSIS configuration files for the packages.
Create an Integration Services catalog.
Install Data Quality Services.
Install Master Data services.
Question No: 97 HOTSPOT – (Topic 2)
A SQL Server Integration Services (SSIS) package is designed to download data from a financial database hosted in SQL Azure.
The connection string to the financial database is defined as a project parameter named FinConStr. The parameter value must be stored securely and must be set explicitly every time the package is executed.
You need to configure the parameter to meet the requirements.
What should you do? (To answer, configure the appropriate option or options in the dialog box in the answer area.)
Question No: 98 DRAG DROP – (Topic 2)
You are developing a SQL Server Integration Services (SSIS) package that loads data into a data warehouse hosted on Windows Azure SQL Database.
You must combine two data sources together by using the ProductID column to provide complete details for each record. The data retrieved from each data source is sorted in ascending order by the ProductID column.
You need to develop a data flow that imports the data while meeting the requirements.
How should you develop the data flow? (To answer, drag the appropriate transformation from the list of transformations to the correct location in the answer area.)
Question No: 99 – (Topic 2)
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?
Question No: 100 – (Topic 2)
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?
SQL Server dtexec utility (dtexec.exe)
SQL Server DTExecUI utility (dtexecui.exe)
SSIS Upgrade Wizard in SQL Server Data Tools
SQL Server Integration Services Deployment Wizard
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.
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|