Slowly Changing Dimensions - SQL Server Integration Services Design Patterns - page 362

WHEN NOT MATCHED THEN
INSERT VALUES (
SRC.ProductAlternateKey
,SRC.ListPrice
,SRC.EnglishDescription
,GETDATE()

-- StartDate

,NULL -- EndDate

)
WHEN MATCHED AND FACT.EndDate is NULL
THEN UPDATE SET FACT.EndDate = GETDATE()
OUTPUT $Action Action_Out
,SRC.ProductAlternateKey
,SRC.ListPrice
,SRC.EnglishDescription
,GETDATE() StartDate

) AS MERGE_OUT
WHERE MERGE_OUT.Action_Out = 'UPDATE'

Conclusion

There are many ways to process SCDs in SSIS. Although the built-in SCD transform
can get you up and running quickly, it may not perform as well as the alternatives. You
may prefer using the Merge pattern due to its overall performance, but having to main-
tain the SQL statements may be an inhibitor in the long run. If you prefer a visual de-
signer experience, consider trying one of the third-party component options.
Table 13-2 summarizes the advantages and disadvantages described in this chapter.

Table 13-2 . Slowly Changing Dimension Processing Patterns

Pattern Use For

Slowly Changing Dimension trans-
form

Quick prototyping
Processing a small number of rows
Very large dimensions

Third-party components Full or historical dimension loads

Small-medium sized dimensions
Non-SQL Server destinations