hi, i have a column that stores the last update date fora dimension table. This column will store the date whena rowwas last updated. i'm using SCD in my dimension table data flow. i can't manage to set the lastupdate column to be modified when a row has been changed. when i set the column as fixed attribute, the column will not change even though it has been updated. if i set the column as changing attribute, the whole column for the table will change regardless it has been updated or not... any idea?

A fixed attribute cannot change. If it does, that's a problem with the definition of the column. In the OLE DB Command to update the "lastupdate" column, you need to ensure you've got an appropriate WHERE clause restricting the update to the appropriate record. Generally, the SCD wizard can handle this for you.

From your post, it looks as though you are trying to use the modified date from the source table. Phil is 100% correct in saying that you can't treat that as a fixed attribute through the SCD component. I would suggest ignoring the ModifiedDate in the SCD component and trying one the following: Assign the modified date after the SCD component. You would lose the ability to use the Modified Date from the source table, so you'd have to use the date the ETL ran (or something similar). Use a merge join to introduce the modified date after the SCD. Performance takes a hit if you do this. Skip the SCD entirely, and use your own logic (through a lookup or script component) to determine whether the row has changed. (See this post for info for a good approach on this - https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1).

thanks John, Phil.... i got it... =D

Could you mark it as answered? Thanks,

err.. i've already marked it as answer on 16 march... and i can only see unmark as answer button now...

My bad... It seems like IE is caching some of these pages for a really long time :)

This thread was not marked as answered as of today when I actually marked. The mark placed by the OP was somehowlost...

I hate to reply to such an old thread - - but this is exactly what I'm looking to do as well. But I'm confused on how to implement options 1 or 2 (I don't like option 3) Option 1 sounds easiest, and is fine by me... but which component should I use to assign the modified date? I simply want to 'update' the records that were modified (the records that go down the updated 'channel' of the SCD) Any idea why I cannot simply edit the update statement in the OLE DB Command in this channel? If I do this, nothing gets updated, and I have to 'refresh' the commend from the SCD. Thank you in advance for any help K

I was able to get it figured out. I believe last night, when I was tired, I wasn't mapping the columns properly. Either that, or I also changed the setting to update all historical records (which is fine in my case, since I'm only maintaining one row per key). Everything works as expected now. Thanks