SSIS alternative to the Slowly Changing Dimension transformation

[via Jamie Thomson]

If you are transforming large dimensions this will be of interest to you.

 Alberto Ferrari has come up with an interesting solution to the performance of the SCD transform task. The built-in SCD transform issues separate SQL statements for each incoming dimension row, Alberto has a custom SSIS component here (http://www.sqljunkies.com/WebLog/aferrari/archive/2006/06/08/TableDifference.aspx) that compares two tables in one operation. It is definitely something I am going to look into next time I have to deal with a large Slowly Changing Dimension.

 

Update [2 Apr 2008]: Alberto has had a new blog for a little while now - you can now find him and the TableDiff component at www.sqlbi.eu

Print | posted on Wednesday, June 14, 2006 7:44 PM

Comments on this post

# re: SSIS alternative to the Slowly Changing Dimension transformation

Requesting Gravatar...
the blog referenced above has gotten away from us. do you have any details or an alternate site?

i am comparing a stage table with a production table and want to set the record status to inactive if missing from the current stage data. right now, we are performing a cross-database query and i would like to implement something which removes the cross-database execute sql task.
Left by possum jones on Apr 02, 2008 4:31 AM

# re: SSIS alternative to the Slowly Changing Dimension transformation

Requesting Gravatar...
I just posted an update with the new location for Alberto's new blog. From what I know, it should allow you to remove the cross-database queries.
Left by Darren Gosbell on Apr 02, 2008 6:28 PM

# re: SSIS alternative to the Slowly Changing Dimension transformation

Requesting Gravatar...
Nice post,

thanks for sharing the ssis alternative...

Thanks for writing, most people don't bother.
Left by software developers on Oct 12, 2009 9:36 PM

# re: SSIS alternative to the Slowly Changing Dimension transformation

Requesting Gravatar...
Try out expressor software. http://www.expressor-software.com.
Left by Michael on Jul 13, 2010 1:28 AM

# re: SSIS alternative to the Slowly Changing Dimension transformation

Requesting Gravatar...
Alberto's component was recently brought under CozyRoc's suite of SSIS extensions (http://www.cozyroc.com) - you can find it there now.
You can also try the Kimball Method SCD component available on CodePlex (http://kimballscd.codeplex.com).
Left by Todd McDermid on Jul 15, 2010 7:58 AM

# re: SSIS alternative to the Slowly Changing Dimension transformation

Requesting Gravatar...
There are more alternatives:
http://microsoft-ssis.blogspot.com/2011/01/slowly-changing-dimension-alternatives.html
Left by Erik on Jan 07, 2011 6:42 PM
comments powered by Disqus