With Lead2pass complete study guide for the Microsoft 70-458 Certification exam you will find questions and answers from previous exams as well as ones that our experts believe will be on the upcoming exams due to upgrades and new releases. This gives you the resources you actually need to pass the exam instead of just studying material without any knowledge of what might be on a test. If you want a career in the IT world, a certification is the only answer to ensure you get your dream job.
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:
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
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
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
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
Being a Microsoft 70-458 certified professional guarantee a better future and success for you. Lead2pass Microsoft certification preparation exams would prove themselves to be the most helpful step in the direction of that Microsoft goal. You can easily find Microsoft preparation material for all certifications in Lead2pass by visiting our website below: