Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. You are using a SQL Server Integration Services (SSIS) project that is stored in the SSIS catalog. An Environment has been defined in the SSIS catalog.
You need to add the Environment to the project.
Which stored procedure should you use?
A. catalog.set_environment_reference_type
B. catalog.set_environment_property
C. catalog.create_environment_reference
D. catalog.create_environment
Answer: C
Q2. You are developing a SQL Server Integration Services (SSIS) project by using the Project Deployment Model.
The project will be deployed to an SSIS catalog folder where Environments have already been created. You need to deploy the project. 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: M
Q3. 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
Q4. 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:
Q5. You are editing a SQL Server Integration Services (SSIS) package that contains a task with a sensitive property.
You need to create a project parameter and configure it so that its value is encrypted when it is deployed to the SSIS catalog.
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:
Q6. You are developing a SQL Server Integration Services (SSIS) package.
You use a Data Profiling task to examine the data from a source system. You need to establish:
. The minimum and maximum dates for the datetime columns in the source data
. The minimum, maximum, and average values for numeric columns in the source data
You need to use the appropriate profile type in the Data Profiling task.
Which profile type should you use? (To answer, select the appropriate profile type in the answer area.)
Answer:
Q7. A SQL Server Integration Services (SSIS) package imports daily transactions from several files into a SQL Server table named Transaction. Each file corresponds to a different store and is imported in parallel with the other files. The data flow tasks use OLE DB destinations in fast load data access mode.
The number of daily transactions per store can be very large and is growing- The Transaction table does not have any indexes.
You need to minimize the package execution time.
What should you do?
A. Partition the table by day and store.
B. Create a clustered index on the Transaction table.
C. Run the package in Performance mode.
D. Increase the value of the Row per Batch property.
Answer: D
Explanation: * Data Access Mode – This setting provides the 'fast load' option which internally uses a BULK INSERT statement for uploading data into the destination table instead of a simple INSERT statement (for each single row) as in the case for other options.
* BULK INSERT parameters include: ROWS_PER_BATCH =rows_per_batch Indicates the approximate number of rows of data in the data file. By default, all the data in the data file is sent to the server as a single transaction, and the number of rows in the batch is unknown to the query optimizer. If you specify ROWS_PER_BATCH (with a value > 0) the server uses this value to optimize the bulk-import operation. The value specified for ROWS_PER_BATCH should approximately the same as the actual number of rows.
Q8. You are maintaining a SQL Server Integration Services (SSIS) package. The package uses custom functionality that is implemented in Microsoft Visual C.
The implementation of the custom functionality changes overtime. The design of the package allows you to deploy new releases of the custom functionality without redeploying the entire package.
You need to implement and deploy an update to the custom functionality without requiring package redeployment.
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. A Data Flow task in a SQL Server Integration Services (SSIS) package produces run-time errors.
You need to edit the package to log specific error messages.
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:
Q10. 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:
Q11. 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. Expression task
B. Script task
C. Execute SQL task
D. Execute Process task
E. Term Extraction transformation
Answer: A
Reference:
http://msdn.microsoft.com/en-us/library/hh213137.aspx
Q12. 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' 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:
Start Time
End Time
Execution Result
Execution Duration
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.)
Answer:
Q13. You are creating a SQL Server Integration Services (SSIS) package to retrieve product data from two different sources. One source is hosted in a SQL Azure database. Each source contains products for different distributors.
Products for each distributor source must be combined for insertion into a single product table destination.
You need to select the appropriate data flow transformation to meet this requirement.
Which transformation types should you use? (Each answer represents a complete solution. Choose all that apply.)
A. Slowly Changing Dimension
B. pivot
C. Lookup
D. Union All
E. Merge
Answer: D,E
Q14. You are creating a Data Quality Services (DQS) solution. You must provide statistics on the accuracy of the data.
You need to use DQS profiling to obtain the required statistics.
Which DQS activity should you use?
A. Cleansing
B. Knowledge Discovery
C. SQL Profiler
D. Matching Rule Definition
Answer: A
Q15. You are implementing the indexing strategy for a fact table in a data warehouse. The fact table is named Quotes. The table has no indexes and consists of seven columns:
[ID]
[QuoteDate]
[Open]
[Close]
[High]
[Low]
[Volume]
Each of the following queries must be able to use a columnstore index:
SELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate]
BETWEEN '20100101' AND '20101231'.
SELECT AVG([High] - [Low]) AS [AverageRange] FROM Quotes WHERE
[QuoteDate] BETWEEN '20100101' AND '20101231'.
SELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate]
BETWEEN '20100101' AND '20101231'.
You need to ensure that the indexing strategy meets the requirements. The strategy must also minimize the number and size of the indexes.
What should you do?
A. Create one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and [QuoteDate].
B. Create three coiumnstore indexes:
One containing [QuoteDate] and [Close]
One containing [QuoteDate], [High], and [Low]
One containing [QuoteDate] and [Volume]
C. Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and
[Volume].
D. Create two columnstore indexes:
One containing [ID], [QuoteDate], [Volume], and [Close]
One containing [ID], [QuoteDate], [High], and [Low]
Answer: C
Reference: http://msdn.microsoft.com/en-us/library/gg492088.aspx
Reference: http://msdn.microsoft.com/en-us/library/gg492153.aspx
Q16. Your company is evaluating the data cleansing capabilities of SQL Server Data Quality Services (DQS).
A stand-alone server will be used to host DQS, as well as all related services necessary to run this service.
You need to install the DQS components on the server by using the minimum amount of steps possible. You also need to grant permissions to a login named DataStewards to enable DataStewards to edit and execute DQS projects.
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: