Implementing a Data Warehouse with Microsoft SQL Server 2012
Question No: 61 – (Topic 1)
You are deploying a new SQL Server Integration Services (SSIS) project to the test environment.
A package in the project uses a custom task component.
You need to ensure that the custom object is deployed on the test environment correctly. What should you do?
Run the package by using the dtexec /rep /conn command.
Create a reusable custom logging component.
Create an OnError event handler.
Use the gacutil command.
Use the dtutil /copy command.
Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
Run the package by using the dtexec /dumperror /conn command.
Use the Project Deployment Wizard.
Deploy the package by using an msi file.
Add a data tap on the output of a component in the package data flow.
Run the package by using the dtexecui.exe utility and the SQL Log provider.
Question No: 62 DRAG DROP – (Topic 1)
You are building a SQL Server Integration Services (SSIS) package to load product data sourced from a SQL Azure database to a data warehouse. Before the product data is loaded, you create a batch record by using an Execute SQL task named Create Batch. After successfully loading the product data, you use another Execute SQL task named Set Batch Success to mark the batch as successful.
You need to create and execute an Execute SQL task to mark the batch as failed if either the Create Batch or Load Products task fails.
Which three steps 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.)
http://msdn.microsoft.com/en-us/library/ms141003.aspx http://msdn.microsoft.com/en-us/library/ms141261.aspx http://msdn.microsoft.com/en-us/library/ms141722.aspx http://msdn.microsoft.com/en-us/library/ms139895.aspx
Question No: 63 DRAG DROP – (Topic 1)
A SQL Server Integration Services (SSIS) package named DataFeed interacts with an external vendor data feed. The package is executed several times a day, either as part of other packages#39; control flow or by itself. The external data feed is unreliable because network failures and slow response times are frequent. The package is currently deployed on the file system.
To analyze the reliability of the external data feed, you must collect execution data. Every time the DataFeed package is executed, the following information must be logged:
You need to design a logging solution that meets the requirements by using the least amount of administrative and development effort.
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.)
Question No: 64 – (Topic 1)
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.)
Change the ORDER BY clause on the product source to order by ProductName.
Change the Merge Join transformation to a Merge transformation.
Set the appropriate SortKeyPosition properties on the data sources.
Set the IsSorted property on both data sources.
Question No: 65 DRAG DROP – (Topic 1)
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.)
http://msdn.microsoft.com/en-us/library/hh479588.aspx 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
Question No: 66 – (Topic 1)
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
http://msdn.microsoft.com/en-us/library/ms345179.aspx http://msdn.microsoft.com/en-us/library/ms141708.aspx http://msdn.microsoft.com/en-us/library/hh213214.aspx http://msdn.microsoft.com/en-us/library/hh213296.aspx http://msdn.microsoft.com/en-us/library/hh213293.aspx
Question No: 67 – (Topic 1)
You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on ServerA. The package includes a data flow and is executed on ServerB. The destination table has its own identity column.
The destination data load has the following requirements:
->The identity values from the source table must be used.
->Default constraints on the destination table must be ignored.
->Batch size must be 100,000 rows.
You need to add a destination and configure it to meet the requirements. Which destination should you use?
OLE DB Destination with Fast Load
SQL Server Destination
ADO NET Destination without Bulk Insert
ADO NET Destination with Bulk Insert
OLE DB Destination without Fast Load
Question No: 68 – (Topic 1)
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?
Install the Analysis Services OLE DB Provider.
Make the data available for DQS operations.
Run the Data Quality Server Installer.
Answer: C Explanation:
Question No: 69 – (Topic 1)
You are using SQL Server Data Tools to develop a SQL Server Integration Services (SSIS) project.
The first package that you create in this project contains a package connection that accesses a flat file. Additional packages in the project must also access this file.
You need to define and reuse the flat file connection in all project packages. What should you do?
Convert the package Connection Manager in the first package to a project Connection Manager.
Copy the package Connection Manager and paste it into the second package.
Convert the project to the Package Deployment model.
Set the ProtectionLevel property of the package Connection Manager to DontSaveSensitive to reuse the flat file connection.
Question No: 70 – (Topic 1)
A SQL Server Integration Services (SSIS) package on a computer is failing periodically in production. The package was created less than one year ago and was deployed to the SSIS catalog.
Sometimes the package is started on a SQL Agent schedule; sometimes the package is started manually by an SSIS developer 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 failed in the past.
Where can you find this information?
the SQL Server Log
the SSISDB.[catalog].[executions] view
the SSISDB.[catalog].[event_messages] view
the SQL Agent Job History
the SQL Agent Error Log
Topic 2, Volume B
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|