Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. 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
Reference: 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
Q2. You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model. All packages in the project must log custom messages.
You need to produce reports that combine the custom log messages with the system-generated log messages. What should you do?
A. Use an event handler for OnError for the package.
B. Use an event handler for OnError for each data flow task.
C. Use an event handler for OnTaskFailed for the package.
D. View the job history for the SQL Server Agent job.
E. View the All Messages subsection of the All Executions report for the package.
F. Store the System::SourceID variable in the custom log table.
G. Store the System::ServerExecutionID variable in the custom log table.
H. Store the System::ExecutionInstanceGUID variable in the custom log table.
I. Enable the SSIS log provider for SQL Server for OnError in the package control flow.
J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow,
K. Deploy the project by using dtutil.exe with the /COPY DTS option.
L. Deploy the project by using dtutil.exe with the /COPY SQL option.
M. Deploy the .ispac file by using the Integration Services Deployment Wizard.
N. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.
O. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
P. Create a SQL Server Agent job to execute the
SSISDB.catalog.create_execution and SSISDB.catalog.start_execution stored procedures.
Q. Create a table to store error information. Create an error output on each data flow destination that writes OnError event text to the table.
R. Create a table to store error information. Create an error output on each data flow destination that writes OnTaskFailed event text to the table.
Answer: G
Q3. A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse.
The SSIS catalog includes two Environments:
. Development
. QA
Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the development or QA data warehouses.
You need to be able to execute deployed packages by using either of the defined Environments.
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.)
Answer:
Q4. pic 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.
. Support several packages with different configuration settings. 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.
Answer:
Q5. 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.
Select “IsolationLeve as Choas”
Answer:
Q6. You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation.
The reference data to be used in the transformation does not change.
You need to reuse the Fuzzy Lookup match index to increase performance and reduce maintenance.
What should you do?
A. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.
B. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
C. Select the DropExistingMatchlndex option in the Fuzzy Lookup Transformation Editor.
D. Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.
E. Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.
Answer: A
Reference: http://msdn.microsoft.com/en-us/library/ms137786.aspx
Q7. 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.)
A. Change the ORDER BY clause on the product source to order by ProductName.
B. Change the Merge Join transformation to a Merge transformation.
C. Set the appropriate SortKeyPosition properties on the data sources.
D. Set the IsSorted property on both data sources.
Answer: C,D
Q8. You are building a fact table in a data warehouse.
The table must have a columnstore index. The table cannot be partitioned.
You need to design the fact table and load it with data.
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.)
Answer:
Q9. You are designing a data warehouse that contains a product dimension.
The product dimension contains the original product attributes as well as the most recent set of updated attributes. When a product is updated, the original product attributes must be preserved.
The product dimension design must:
. Support a Type 3 Slowly Changing Dimension (SCD) process
. Minimize the total storage consumed
You need to design a solution that meets these requirements.
What should you do?
A. Design two tables. One table will hold the original product surrogate key and attributes. The other table will hold the most recent product surrogate key and attributes.
B. Design a table as a Type 2 SCD dimension that holds the surrogate key for each product and its attributes. Limit the number of records per product within the table to two.
C. Design a table to hold the surrogate key for each product and its attributes as well as additional columns to store the original attributes.
D. Design a table to hold the surrogate key for each product and its attributes. Add a column to store an indicator that identifies the record as original data or recent data.
Answer: C
Q10. You are developing a SQL Server Integration Services (SSIS) package.
You need to design a package to change a variable value during package execution by using the least amount of development effort.
What should you use?
A. Express on task
B. Data Cleansing transformation
C. Fuzzy Lookup transformation
D. Term Lookup transformation
E. Data Profiling task
Answer: A
Q11. You are reviewing the design of a customer dimension table in an existing data warehouse hosted on SQL Azure.
The current dimension design does not allow the retention of historical changes to customer attributes such as Postcode.
You need to redesign the dimension to enable the full historical reporting of changes to multiple customer attributes including Postcode.
What should you do?
A. Add StartDate and EndDate columns to the customer dimension.
B. Add an IsCurrent column to the customer dimension.
C. Enable Snapshot Isolation on the data warehouse.
D. Add CurrentValue and PreviousValue columns to the customer dimension.
Answer: A
Q12. 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. Option A
B. Option B
C. Option C
D. Option D
Answer: D
Reference: http://msdn.microsoft.com/en-us/library/bb522511.aspx
Reference: http://msdn.microsoft.com/en-us/library/bb510627.aspx
Reference: http://msdn.microsoft.com/en-us/library/cc645937.aspx
Q13. You are performance tuning a SQL Server Integration Services (SSIS) package to load sales data from a source system into a data warehouse that is hosted on Windows Azure SQL Database.
The package contains a data flow task that has seven source-to-destination execution trees.
Only three of the source-to-destination execution trees are running in parallel.
You need to ensure that all the execution trees run in parallel.
What should you do?
A. Set the EngineThreads property of the data flow task to 7.
B. Set the MaxConcurrentExcecutables property of the package to 7.
C. Create seven data flow tasks that contain one source-to-destination execution tree each.
D. Place the data flow task in a For Loop container that is configured to execute seven times.
Answer: A
Q14. You are creating a SQL Server Integration Services (SSIS) package to populate a fact table from a source table. The fact table and source table are located in a SQL Azure database. The source table has a price field and a tax field. The OLE DB source uses the data access mode of Table.
You have the following requirements:
. The fact table must populate a column named TotalCost that computes the sum of the price and tax columns. . Before the sum is calculated, any records that have a price of zero must be discarded.
You need to create the SSIS package in SQL Server Data Tools.
In what sequence should you order four of the listed components for the data flow task? (To answer, move the appropriate components from the list of components to the answer area and arrange them in the correct order.)
Answer:
Q15. You are developing a SQL Server Integration Services (SSIS) package.
The package uses custom functionality that accesses a SQL Server database. The custom functionality must be implemented by using Language Integrated Query (LINQ).
You need to ensure that the LINQ code can be debugged at design time.
What should you select from the SSIS Toolbox? (To answer, drag the appropriate item to
the correct location in the answer area.)
Answer:
Q16. You develop a SQL Server Integration Services (SSIS) package that imports SQL Azure data into a data warehouse every night.
The SQL Azure data contains many misspellings and variations of abbreviations. To import the data, a developer used the Fuzzy Lookup transformation to choose the closest-matching string from a reference table of allowed values. The number of rows in the reference table is very large.
If no acceptable match is found, the Fuzzy Lookup transformation passes a null value.
The current setting for the Fuzzy Lookup similarity threshold is 0.50.
Many values are incorrectly matched.
You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading performance.
What should you do?
A. Change the Exhaustive property to True.
B. Decrease the maximum number of matches per lookup.
C. Change the similarity threshold to 0.85.
D. Increase the maximum number of matches per lookup.
Answer: B