July 4, 2011 at 8:01 am
What do you think are the pro's and cons of using the SCD object in SSIS, do you think its better to use the SSIS object or doing it via t-sql in a store proc.
July 4, 2011 at 8:04 am
using the task in SSIS is slow and causes row-by-row operations to happen, also changes are difficult to implement. The one advantage is that it is easy to setup.
using t-sql (merge in 2008) means that you can perfrom set based operations which are much faster and way more robust, also modifications are easier.
my vote will also be for t-sql, unless the dataset is very small..
July 5, 2011 at 1:17 am
You can ofcourse create your own set based SCD out of stock SSIS components or use one of the custom SCD components available on the interwebs.
July 5, 2011 at 1:35 am
If you are interested, Andy Leonard has a good write up on set based updates here:
July 6, 2011 at 1:46 am
I like the SCD because of its ease of use BUT I only use it in packages where performance isn't an issue - those which run overnight as the sole user of the database for example. Most of my packages are in SQL 2005 so merge isn't available.
As with everything it's horses for courses and picking the right tool for the job.
July 11, 2011 at 3:04 am
I never use the SCD component. Ever. And never will.
By the time I have set-up the component, I've made a similar set-up in TSQL or with other SSIS components. That set-up will always outperform the SCD component (OK, for just a single row there won't be much difference :-D).
To give you an idea: I recently changed a package that loaded a little less than 2 million rows. When it used SCD components (and badly configured Lookup components), it ran for more that 6 hours. When using TSQL, it run under 10 minutes.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply