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
What is SCD logic?
The Slowly Changing Dimension transformation coordinates the updating and inserting of records in data warehouse dimension tables. For example, you can use this transformation to configure the transformation outputs that insert and update records in the DimProduct table of the AdventureWorksDW2012 database with data from the Production.Products table in the AdventureWorks OLTP database.
It is definitely not MERGE component. Whatever ‘SCD logic’ is meant to imply in the question, the most likely/logical answer would be SCD component.
Even if Type 3 SCD isn’t supported by implementing ‘SCD logic’ with a SCD component, a MERGE component still seems a bit far-fetched. A Merge T-SQL statement might be more effective, or even a Script Component.
**just my thought**
Definitely not SCD Component because it does not support Type 3.
T-SQL MERGE within a Script Component seems like a valid option.
A or D
Type 3 is just type 1 and type 2. You can use two scd items and merge join them together. You can write a script to do type 3.
Anyway, SCD is a partial solution in combination with other things. Script is probably the answer because theoretically script component can be the answer to everything.
Also script component is different from script task. Does the script component support tsql?
Also look at this question.
http://www.aiotestking.com/microsoft/you-need-to-add-a-task-or-component-to-the-package-that-allows-you-to-implement-the-scd-logic-5/
This question basically admits that the scd with a merge is the answer to the questin linked above
Since SSIS SCD wizard does not supports Type 3, we have to accept D as right answer.