Not able to update the rows by using Slowly Changing Dimension transformation

Question

Not able to update the rows by using Slowly Changing Dimension transformation

MSKR

SSC-Addicted

Points: 492
More actions
July 7, 2009 at 6:36 am

#132608

Hi
I have a requirement similar as slowly changing dimensions(If record exists update else insert) this can be achive using the SCD transformation in SSIS. I tried it is working fine for the insert and for update its not updating the if any changes accoured in the dimension table.but its redirecting the rows which has come for update.
The process im fallowing is source is connected to SCD transformation and in SCD transformation im defining the Bussiness keys ans using the property changing attribute.
can any any one help me out why the row is not getting updated???
Thanks in Advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Dave Klug SSCertifiable Points: 5811 More actions · Answer 1

If all you need to do is insert/update based on the existence of data, the SDC transform is not the one to use. It's designed for doing Type 1 and 2 changes in data warehousing; where Type 1 is an update if the row exists and type 2 is add a new row if the row exists and then expire the previous row. In both cases, if the data is not in the table, a new row is added.

For what you're doing, use a lookup transform first to check for row existence (set to ignore errors if the row is not found) followed by a conditional split. If the row exists, insert using the OLE DB Destination. If it doesn't exist, use either an OLE DB Command for the update or insert to a staging table and then insert. If you're doing a high volume of transactions, the staging approach is faster as the OLE DB Command processes rows one-by-one.

Wesley Norton SSCrazy Points: 2827 More actions · Answer 2

I had to do something very similar this week, this link helped out immensely.

Wesley Norton SSCrazy Points: 2827 More actions · Answer 3

I had to do something very similar this week, this link helped out immensely.

jreece Ten Centuries Points: 1138 More actions · Answer 4

I am not real clear on your requirements, but if what you need to do is insert a record if it does not exist in the table, and update otherwise if the data has changed (I like this - it allows me to monitor activity in the source data systems), then try downloading the CheckSum transformation component here http://www.sqlis.com/post/Checksum-Transformation.aspx. It takes a little time to configure, but I liked it alot better than the SCD transformation, primarily because it was so much faster.

Jean