Incorporate t-SQL Merge Operation in SSIS

SQL Server introduced in SQL Server 2008. It is very useful statement specially for data warehousing environment. So, it is very likely that you will need it in SSIS project. The can be two solution to incorporate in SSIS.

First One:

Using the merge join operator, do the followings:

1. Merge Join - Source Left join Destination
2. Conditional Split -
• Destination ID is null - "OLEDB Destination"
• Destination ID is not null - "OLEDB Command". Use update operation within "OLEDB Command"

Second One:

Using the "Slowly Changing Dimension" operator, do the followings -

1. Add the "Slowly Changing Dimension" and configure it using wizard
2. Using OLEDB Connection, choose the destination table and set its ID as "Business key"
3. After clicking next, select all other destination columns and select the Change Type as "Changing attribute"
4. Keep all others default.

If you compare these two figures, you will find that how easy it is. The SCD really does a lot for you. It encapsulates sort, merge join and conditional split and auto generate the OLE DB command. So, no programmer forgets to use it instead of using the first option or calling script task from SSIS and make the code less programmer friendly.

If the table size is large, then the first option should be used. On the other hand, if the table size is small, performance of first and second option is almost same. For detail, please read this article -
http://chrisjarrintaylor.co.uk/