I am just in a process of starting a new task, wherein in i need to load Hybrid Dimension Table with SCD1 and SCD2. This need to be achieved as a SSIS Package. Can someone guide what would be the best way dealing this in SSIS, should i used SCD component or there is other way? What are the best practices for this.
For SCD2 type, am using Merge statement.
Thanks
That's a can of worms :)
There are basically four ways to handle SCDs in SSIS: 1. Using the built-in SCD component 2. "Rolling your own" using Lookups, Conditional Splits, Derived Columns, and various destinations. 3. Using T-SQL MERGE 4. Using the third party Kimball SCD component
I'll alert you to my bias towards #4 - I wrote it. But here's my analysis of the bunch.
1 is a good solution for "small" and "easy" dimensions. Why is it good? It's understandable, handles SCD 1 and 2, and is easy to set up. But why only "small" and "easy" dimensions? Because it uses an internal uncached lookup (RBAR) that can't be improved. Because if you change anything in it (re-run the wizard), it destroys any changes you've made to the data flow. And because it won't handle rows where case sensitivity isn't important, or trailing spaces aren't important.
2 is a good solution for larger dimensions. It's good because it performs pretty well, and is "well documented" in that you can see exactly what it's doing from the names of the components you use and how they're put together. It's also easy to manipulate and change how it operates. The downside is that it takes time to set up and test.
3 is a good solution for huge dimensions. It usually outperforms all other alternatives. But that's about all it has going for it. It's very complex to code, and not very understandable without tons of comments.
4 is a good solution for just about any size except maybe "huge" dimensions. It's "easy" to use like the stock SCD component, performs as good or better than 2, and is as configurable as 2.