Implementing a Data Warehouse with Microsoft SQL Server 2012
Question No: 81 HOTSPOT – (Topic 2)
You are designing a SQL Server Integration Services (SSIS) package configuration strategy.
The package configuration must meet the following requirements:
->Include multiple properties in a configuration.
->Force packages to load all settings in the configuration.
->Support Encrypting File System (EFS) formats.
You need to select the appropriate configuration. Which configuration type should you use?
To answer, select the appropriate option from the drop-down list in the dialog box.
Question No: 82 – (Topic 2)
You are designing a SQL Server Integration Services (SS1S) package that uploads a file to a table named Orders in a SQL Azure database.
The company#39;s auditing policies have the following requirements:
鈥n entry must be written to a dedicated SQL Server log table named OrderLog.
鈥he entry must be written as soon as the file upload task completes.
You need to meet the company#39;s policy requirements. Which event handler should you use?
Question No: 83 – (Topic 2)
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 answer presents a part of the solution.
Choose all that apply.)
set the appropriate SortKeyPosition properties on the data sources.
set the ValidateExternalMetaData property on the Merge Join transformation to True.
set the IsSorted property on both data sources.
Set the MaxBuffersPerlnput property on the Merge Join transformation to 2.
Answer: A,C Explanation: References:
http://msdn.microsoft.com/en-us/library/ms141775.aspx http://msdn.microsoft.com/en-us/library/ms137653.aspx http://siddhumehta.blogspot.com/2009/05/validateexternalmetadata-property.html http://msdn.microsoft.com/en-us/library/ms135950.aspx
Question No: 84 – (Topic 2)
A SQL Server Integration Services (SSIS) package was deployed two weeks ago with the Project Deployment Model.
Sometimes the package is started as part of a multistep SQL job. At other times, the package is started manually by a database administrator 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 executes.
How can you find this information?
In the SSISDB.[catalog], query the .[executions] view.
In the SSISDB.[catalog] , query the [event_messages] view.
In SQL Server Management Studio, view the SQL Agent Job History.
In SQL Server Management Studio, view the SQL Agent Error Log.
In SQL Server Management Studio, view the SQL Server Log.
Question No: 85 – (Topic 2)
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.
鈥he Connection Managers used in the package must be configurable without editing the package.
鈥he 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?
Use the gacutil command.
Use the dtutil /copy command.
Use the Project Deployment Wizard.
Create an OnError event handler.
Create a reusable custom logging component.
Run the package by using the dtexec /rep /conn command.
Run the package by using the dtexec /dumperror /conn command.
Run the package by using the dtexecui.exe utility and the SQL Log provider.
Add a data tap on the output of a component in the package data flow.
Deploy the package by using an msi file.
Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
Question No: 86 – (Topic 2)
You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables.
You have the following requirements:
鈥nsure that the deployment model supports changing the content of connection strings by using parameters at execution time.
鈥nsure that the deployment model automatically starts from calls to the catalog.start_execution stored procedure in the SSISDB database.
鈥aximize performance at execution time.
鈥inimize development effort.
You need to design a solution that meets the requirements.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
Use a project deployment model. Modify connection manager properties to use project parameters. Ensure that the SSISDB database is created.
Use a project deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package.
Use a package deployment model. Use a SQL Server package configuration with a common filter. Change the contents of the SSIS Configurations table at runtime.
Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments.
Question No: 87 HOTSPOT – (Topic 2)
You are editing a SQL Server Integration Services (SSIS) package. You need to edit the Script task that has an expression defined.
Which task should you select? To answer, select the appropriate setting or settings in the answer area.
Question No: 88 DRAG DROP – (Topic 2)
A new dedicated server is used to execute resource-intensive SQL Server Integration Services (SSIS) 2012 packages. The environment that you are deploying the packages to
has the following constraints:
->The operating system is Windows Server 2008 R2.
->The SSIS packages are stored in the SSIS catalog.
->Some of these SSIS packages use 32-bit custom components.
You need to install only the components that are required to deploy and run the packages on the new server.
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.)
Note: Step 1:
If you require 32-bit versions of the Integration Services runtime and tools to run certain packages in 32-bit mode, you must also install SQL Server Data Tools (SSDT).
For a complete installation of Integration Services, together with the tools and documentation for developing and managing packages, select both Integration Services and the following Shared Features:
SQL Server Data Tools to install the tools for designing packages.
Management Tools – Complete to install SQL Server Management Studio for managing packages.
Client Tools SDK to install managed assemblies for Integration Services programming.
Step 3: How to Modify the Service Configuration File to Connect to a Named Instance of SQL Server
To connect to a named instance of SQL Server, you must modify the service configuration file, MsDtsSrvr.ini.xml.
Question No: 89 DRAG DROP – (Topic 2)
You are developing a SQL Server Integration Services (SSIS) package that imports unsorted data into a data warehouse hosted on SQL Azure.
You have the following requirements:
鈥 destination table must contain all of the data in two source tables.
鈥uplicate records must be inserted into the destination table.
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.)
http://msdn.microsoft.com/en-us/library/ms141703.aspx http://msdn.microsoft.com/en-us/library/ms141775.aspx http://msdn.microsoft.com/en-us/library/ms141020.aspx http://msdn.microsoft.com/en-us/library/ms140182.aspx
Question No: 90 DRAG DROP – (Topic 2)
You administer a Microsoft SQL Server database. You want to import data from a text file to the database.
You need to ensure that the following requirements are met:
->Data import is performed by using a stored procedure.
->Data is loaded as a unit and is minimally logged.
Which data import command and recovery model should you choose? (To answer, drag the appropriate data import command or recovery model to the appropriate location or locations in the answer area. Each data import command or recovery model may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.)
http://msdn.microsoft.com/en-us/library/ms162802.aspx http://msdn.microsoft.com/en-us/library/ms188365.aspx http://msdn.microsoft.com/en-us/library/ms175937.aspx http://msdn.microsoft.com/en-us/library/aa337544.aspx
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|