Delete record generation using change capture

Ragunathan Gunasekaran · Tue Sep 01, 2009 7:43 am

Hi,
I am generating datasets for loading a type 2 dimension table.
The requirement is to capture the delete records from the change capture stage and using the delete record's key i have to update the dimensio table with an end date.

When i use the change capture stage, i was able to retrieve the records only from the after dataset and not the before dataset. i would like to get the records from the before dataset just because we can't have a delete record available in the after dataset.

Any clues please...?

chulett · Post by **chulett** » Tue Sep 01, 2009 7:48 am

I'm not following your issue. You get 'deletes' for records that are in the before but not in the after.

miwinter · Post by **miwinter** » Tue Sep 01, 2009 7:58 am

Me either... that description's a tad confusing

we can't have a delete record available in the after dataset

You can have a delete record in the change dataset, as it denotes that a record doesn't exist in the after dataset, in comparison to the before dataset. It shows the changes required on the before dataset to 'get to' the after dataset - the nature of how change capture operates. Moreover, the after dataset drives the change dataset, in metadata terms.

Ragunathan Gunasekaran · Tue Sep 01, 2009 8:17 am

Sorry for the confusion. The problem was i was not able to get delete records out of the change capture..In the sense i was getting a null value for all th columns in the record which was designated as a delete record.

But on analysis i found that its not that change capture stage is causing problem but an subsequent lookup stage failure caused a null in the delete column ..

Sorry i haven't posted the complete design keeping in mind that i wouldn't have got problem due to other stages..

Thanks for the reply ... marking this topic to resolved now