Q1. DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package.
The package contains several tasks that must repeat until an expression evaluates to FALSE.
You need to add and configure a container to enable this design.
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:
Explanation:
Box 1: Open the Control Flow designer of the package. Box 2: Add and edit a For Loop container.
Box 3: Configure the EvalExpression property.
Note:
* You create the control flow in a package by using the control flow designer.
Integration Services includes three types of containers that you can use in a control flow.
/ Foreach Loop container
/ For Loop container
/ Sequence container
* For Loop Container
The For Loop container defines a repeating control flow in a package. The loop implementation is similar to the For looping structure in programming languages. In each repeat of the loop, the For Loop container evaluates an expression and repeats its workflow until the expression evaluates to False.
The For Loop container uses the following elements to define the loop:
/ An optional initialization expression that assigns values to the loop counters.
/ An evaluation expression that contains the expression used to test whether the loop
should stop or continue.
/ An optional iteration expression that increments or decrements the loop counter.
Q2. You are installing SQL Server Data Quality Services (DQS).
You need to give users belonging to a specific Active Directory group access to the Data Quality Server.
Which SQL Server application should you use?
A. Data Quality Client with administrative credentials
B. SQL Server Configuration Manager with local administrative credentials
C. SQL Server Data Tools with local administrative permissions
D. SQL Server Management Studio with administrative credentials
Answer: D
Q3. You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD).
You need to add a task or component to the package that allows you to implement the SCD logic.
What should you use?
A. a Script component
B. an SCD component
C. an Aggregate component
D. a Merge component
Answer: D
Q4. You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD).
You need to add a task or component to the package that allows you to implement the SCD logic.
What should you use?
A. a Data Conversion component
B. an Execute SQL task that executes a MERGE statement on the database
C. a Merge component
D. an Expression task
Answer: C
Explanation:
Note: Type of Slowly Changing Dimensions Slowly Changing Dimensions are categorized into three types named: Type 1, Type 2, and Type3. The Type 1 SCD does not maintain the history of changing attributes, it overwrites values of the attributes. Type 2 maintains historical values for changing attributes. Type 3 that we do not use much maintains separate columns for changed attributes. SSIS SCD wizard supports both Type 1 and Type 2.
Using MERGE instead of SCD wizard Replacement of SCD wizard with MERGE is not a straightforward technique. If the SCD has both Type 1 and Type 2 types attributes, they need to be handled separately.
Q5. DRAG DROP
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:
Explanation:
1. OLEDB Source
2. Conditional Split
3. Derived Column
4. OLEDB Destination
Q6. You are designing a SQL Server Integration Services (SSIS) 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. OnPostExecute
B. OnWarning
C. OnExecStatusChanged
D. OnVariableValueChanged
Answer: A
Explanation:
Reference: http://msdn.microsoft.com/en-us/library/ms140223.aspx
Q7. DRAG DROP
You are loading a dataset into SQL Server. The dataset contains numerous duplicates for the Artist and Song columns.
The values in the Artist column in the dataset must exactly match the values in the Artist domain in the knowledge base. The values in the Song column in the dataset can be a close match with the values in the Song domain.
You need to use SQL Server Data Quality Services (DQS) to define a matching policy rule to identify duplicates.
How should you configure the Rule Editor? (To answer, drag the appropriate answers to the answer area.)
Answer:
Q8. DRAG DROP
You use SQL Server Integration Services (SSIS) for extract, transformation, load (ETL) processing.
Issues concerning addresses are discovered in the data warehouse that you manage. Validation must separate the addresses into three categories:
✑ Valid addresses
✑ Autocorrected addresses
✑ Invalid addresses
You need to enhance the SSIS packages to perform address validation by using an external service.
Which transformation should you use? (To answer, drag the appropriate transformation from the list of options to the correct location or locations in the answer area.)
Answer:
Q9. DRAG DROP
You are editing a SQL Server Integration Services (SSIS) package that uses checkpoints.
The package performs the following steps:
1. Download a sales transaction file by using FTP.
2. Truncate a staging table.
3. Load the contents of the file to the staging table.
4. Merge the data with another data source for loading to a data warehouse.
The checkpoints are currently working such that if any of the four steps fail, the package will restart from the failed step the next time it executes.
You need to modify the package to ensure that if either the Truncate Staging Table or the Load Sales to Staging task fails, the package will always restart from the Truncate Staging Table task the next time the package runs.
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:
Q10. You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart.
You have the following requirements:
•Ensure that the process supports the creation of an exception report that details possible duplicate key values, null ratios within columns, and column-length distributions of values.
•Ensure that users can generate the exception report in an XML format.
•Use the minimum development effort.
You need to design the SSIS solution to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Use a Data Profiling task. Use a Data Flow task to extract the XML output of the Data Profiling task into a SQL Server table. Query the table to view the exceptions.
B. Use Data Flow tasks to process the clean data.
C. Use a Data Profiling task. Read the exceptions in Data Profile Viewer.
D. Design a stored procedure that examines data for common dirty data patterns. Use an Execute SQL task.
Answer: C