Microsoft 70-458 is often called the hardest of all Microsoft exams. GreatExam helps you kill the Microsoft 70-458 exam challenge and achieve the perfect passing score with its latest practice test, packed into the revolutionary interactive VCE. This is the best way to prepare and pass the 70-458 exam.
QUESTION 21
Drag and Drop Question
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.)
Answer:
Explanation:
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 22
Drag and Drop Question
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.)
Answer:
Explanation:
http://msdn.microsoft.com/en-us/library/ms137690.aspx
http://msdn.microsoft.com/en-us/library/ms141144.aspx
QUESTION 23
Drag and Drop Question
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 Development!.
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.)
Answer:
Explanation:
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
QUESTION 24
Drag and Drop Question
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.)
Answer:
Explanation:
http://msdn.microsoft.com/en-us/library/ms141821.aspx
http://msdn.microsoft.com/en-us/library/bb895366.aspx
QUESTION 25
You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks.
The three Execute SQL tasks modify products in staging tables in preparation for a data warehouse load. The package and all three Execute SQL product tasks have their TransactionOption property set to Supported.
You need to ensure that if any of the three Execute SQL product tasks fail, all three tasks will roll back their changes.
What should you do?
A. Change the TransactionOption property of the package to Required.
B. Change the TransactionOption property of all three Execute SQL product tasks to Required.
C. Move the three Execute SQL product tasks into a Foreach Loop container.
D. Move the three Execute SQL product tasks into a Sequence container.
Answer: A
Explanation:
http://msdn.microsoft.com/en-us/library/ms137690.aspx
http://msdn.microsoft.com/en-us/library/ms141144.aspx
QUESTION 26
Hotspot Question
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:
Explanation:
http://msdn.microsoft.com/en-us/library/ms137690.aspx
http://msdn.microsoft.com/en-us/library/ms137749.aspx
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtscontainer.isolationlevel.aspx
http://msdn.microsoft.com/en-us/library/ms173763.aspx
QUESTION 27
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’s auditing policies have the following requirements:
– An entry must be written to a dedicated SQL Server log table named OrderLog.
– The entry must be written as soon as the file upload task completes.
You need to meet the company’s policy requirements.
Which event handler should you use?
A. OnProgress
B. Onlnformation
C. OnPostExecute
D. OnComplete
Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/ms140223.aspx
QUESTION 28
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?
A. .dtsConfig file
B. Registry Entry
C. Environment Variable
D. Parent Package Variable
E. XML Configuration File
Answer: C
Explanation:
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 29
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series.
Each question is independent of the other questions in this series.
Information and details provided in a question apply only to that question.
You are troubleshooting an existing SQL Server Integration Services (SSIS) package.
On several occasions, the package execution does not finish and no data seems to have been transferred.
You need to ensure that package logging occurs.
Your solution must minimize deployment and development efforts.
What should you do?
A. Add an OnError event handler to the SSIS project.
B. Use an msi file to deploy the package on the server.
C. Open a command prompt and run the gacutil command.
D. Open a command prompt and run the dtutil /copy command.
E. Open a command prompt and run the dtexec /rep /conn command.
F. Open a command prompt and run the dtexec /dumperror /conn command.
G. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
H. Create a reusable custom logging component and use it in the SSIS project.
I. Configure the SSIS solution to use the Project Deployment Model.
J. Configure the output of a component in the package data flow to use a data tap.
K. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
Answer: A
Explanation:
http://msdn.microsoft.com/en-us/library/ms141212.aspx http://www.mssqltips.com/sqlservertip/2450/ssis-package-deployment-model-in-sql-server-2012- part-1-of-2/
http://www.mssqltips.com/sqlservertip/2450/ssis-package-deployment-model-in-sql-server-2012- part-2-of-2/
QUESTION 30
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series.
Each question is independent of the other questions in this series.
Information and details provided in a question apply only to that question.
You are developing a SQL Server Integration Services (SSIS) project that copies a large amount of rows from a SQL Azure database,
The project uses the Package Deployment Model.
This project is deployed to SQL Server on a test server.
You need to ensure that the project is deployed to the SSIS catalog on the production server. What should you do?
A. Add an OnError event handler to the SSIS project.
B. Use an msi file to deploy the package on the server.
C. Open a command prompt and run the gacutil command.
D. Open a command prompt and run the dtutil /copy command.
E. Open a command prompt and run the dtexec /rep /conn command.
F. Open a command prompt and run the dtexec /dumperror /conn command.
G. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility.
H. Create a reusable custom logging component and use it in the SSIS project.
I. Configure the SSIS solution to use the Project Deployment Model.
J. Configure the output of a component in the package data flow to use a data tap.
K. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server.
Answer: I
Explanation:
http://msdn.microsoft.com/en-us/library/hh231102.aspx
http://msdn.microsoft.com/en-us/library/hh213290.aspx
http://msdn.microsoft.com/en-us/library/hh213373.aspx
QUESTION 31
Drag and Drop Question
You are designing an extract, transform, load (ETL) process with SQL Server Integration Services (SSIS).
Two packages, Package A and Package B, will be designed.
Package A will execute Package B.
Both packages must reference a file path corresponding to an input folder where files will be located for further processing.
You need to design a solution so that the file path can be easily configured with the least administrative and development effort.
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.)
Answer:
Explanation:
http://msdn.microsoft.com/en-us/library/hh479588.aspx
http://msdn.microsoft.com/en-us/library/hh213290.aspx
http://msdn.microsoft.com/en-us/library/hh213373.aspx
QUESTION 32
You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse.
You add an Execute SQL task to the control flow.
The task must execute a simple INSERT statement.
The task has the following requirements:
– The INSERT statement must use the value of a string package variable. The variable name is StringVar.
– The Execute SQL task must use an OLE DB Connection Manager. In the Parameter Mapping tab of the Execute SQL task, StringVar has been added as the only parameter.
You must configure the SQLStatement property of the Execute SQL task.
Which SQL statement should you use?
A. INSERT INTO dbo.Table (variablevalue) VALUES ($Project::StringVar)
B. INSERT INTO dbo.Table (variablevalue) VALUES (@StringVar)
C. INSERT INTO dbo.Table (variablevalue) VALUES ($Package::StringVar)
D. INSERT INTO dbo.Table (variablevalue) VALUES (?)
Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/ms141003.aspx
http://msdn.microsoft.com/en-us/library/ms140355.aspx
http://msdn.microsoft.com/en-us/library/cc280502.aspx
QUESTION 33
Drag and Drop Question
You are building a SQL Server Integration Services (SSIS) package to load data from all files that are automatically copied to a directory each night through an external FTP process.
You need to load data from all copied files to a destination table in SQL Server.
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.)
Answer:
Explanation:
http://msdn.microsoft.com/en-us/library/ms141724.aspx
http://msdn.microsoft.com/en-us/library/ms137728.aspx
QUESTION 34
You are editing a SQL Server Integration Services (SSIS) package that contains three Execute SQL tasks and no other tasks.
The package and all three Execute SQL tasks have their TransactionOption property set to Supported.
You need to ensure that if any of the Execute SQL tasks fail, all three tasks will roll back their changes.
What should you do?
A. Change the TransactionOption property of the package to Required.
B. Change the TransactionOption property of all three Execute SQL tasks to Required.
C. Move the three Execute SQL tasks into a Sequence container.
D. Move the three Execute SQL tasks into a Foreach Loop container.
Answer: A
Explanation:
http://msdn.microsoft.com/en-us/library/ms137749.aspx
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.dtstransactionoption.aspx
QUESTION 35
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?
A. For each target server, create separate Environments in the SSIS catalog of the host
SQL Server SSIS instance.
Select the appropriate Environment at package execution time.
B. Create one SSIS catalog Environment.
Change the values of each Environment variable at package execution time.
C. For each target server, create separate registry entry configurations.
Select the registry entry at package execution time.
D. For each target server, create a separate XML configuration file.
Select the XML configuration file at package execution time.
Answer: A
QUESTION 36
You are developing a SQL Server Integration Services (SSIS) package that imports data from a relational database to a data warehouse.
You are importing data from a relational table named Projects.
The table has change data capture enabled on all columns.
You need to process only the most recent values from rows that have been inserted or updated since the previous execution of the package.
Which query should you use as the data source?
A. SELECT * FROM cdc.fn_cdc_get_all_changes_Projects (@from_lsn, @to_lsn, N’ all update old’)
B. SELECT * FROM cdc.fn_cdc_get_all_changes_Projects (@from_lsn, @to_lsn, N’ all’)
C. SELECT * FROM cdc.fn_cdc_get_net_changes_Projects (@from_lsn, @to_lsn)
D. SELECT * FROM cdc.Projects_CT WHERE @from_lsn >= _$start_lsn AND @to_lsn < _$start_lsn
Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/bb522511.aspx
http://msdn.microsoft.com/en-us/library/bb510627.aspx
http://msdn.microsoft.com/en-us/library/cc645937.aspx
QUESTION 37
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?
A. ADO NET Destination with Bulk Insert
B. SQL Server Destination
C. OLE DB Destination with Fast Load
D. ADO NET Destination without Bulk Insert
E. OLE DB Destination without Fast Load
Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/ms141237.aspx
http://msdn.microsoft.com/en-us/library/ms139821.aspx
http://msdn.microsoft.com/en-us/library/ms141095.aspx
QUESTION 38
You are adding a new capability to several dozen SQL Server Integration Services (SSIS) packages.
The new capability is not available as an SSIS task.
Each package must be extended with the same new capability.
You need to add the new capability to all the packages without copying the code between packages.
What should you do?
A. Use the Expression task.
B. Use the Script component.
C. Use the Script task.
D. Develop a custom task.
E. Develop a custom component.
Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/ms135965.aspx
http://msdn.microsoft.com/en-us/library/ms345161.aspx
QUESTION 39
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 SQL Azure.
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?
A. Source Assistant
B. Audit
C. Destination Assistant
D. Script Component
Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/ms137640.aspx
http://msdn.microsoft.com/en-us/library/ms141150.aspx
http://msdn.microsoft.com/en-us/library/ff929138.aspx
http://msdn.microsoft.com/en-us/library/ff929116.aspx
QUESTION 40
Hotspot Question
You are developing a data flow to load sales data into a fact table.
In the data flow, you configure a Lookup Transformation in full cache mode to look up the product data for the sale.
The lookup source for the product data is contained in two tables.
You need to set the data source for the lookup to be a query that combines the two tables.
Which page of the Lookup Transformation Editor should you select to configure the query? To answer, select the appropriate page in the answer area.
Answer:
Explanation:
http://msdn.microsoft.com/en-us/library/ms141821.aspx
http://msdn.microsoft.com/en-us/library/ms189697.aspx
GreatExam VCE dumps only include latest 70-458 exam questions, so you will know exactly what to expect on your real exam. GreatExam products are industry’s most reliable and convenient tool to prepare for the 70-458 exam.