Change Data Capture

Again, looking for the best way to do this with SSIS.

I have a source table and I'd like to load it to a database daily, capturing what changed.

This is not a dimentional table but a fact table.

So, what I;d need to do for each record is to see if the record already exists (using business key) and if it does - compare some of the data fields and of there are changes - register it somehow and if not changes ignore.

Right now, the only two ways I see to do it with SSIS:

- Use Slowly Chaging Dimentions transformation

- Use Lookup and customize SQL, adding something like: WHERE key = ? and (field1 <> ? or field2 <> ?...)

I was wondering of there an easy way.

Dima.

Use checksums... I have something about that on my blog, you can download the component on www.sqlis.com...|||

this is not bad! not bad at all...

and it fits my case, since I have around 80 columns to check.

I wish they extened data destination column mapping to do this. If they added an Option column on the mapping screen with things like: "Key", "Track Changes", "Overwrite" - this would be so powerfull tool.