http://www.sqlservercentral.com/articles/EDW/77100/
I came across this the other day, when I was looking at using the SQL MERGE statement to replicate the functionality of the Slowly Changing Dimension task in SSIS. There are many benefits to using MERGE instead of the SSIS task, one of my favourites being only having to run 1 SQL statement for the whole task, instead of one for each Lookup, and then subsequent UPDATE or INSERT statement.
This link however, takes it one step further by dynamically building the MERGE statements to use in your ETL. This removes the need to manually update scripts when the table schema changes, such as adding new fields, and makes it a great candidate for quick deployment of Slowly Changing Dimension tasks.