Change Capture Stage Problem

[varshanswamy]

Hi,

I am in the process of using a change capture stage as a replacement for checking if data being processed every day if it is present in the database, if it is present then we update a set of fields, otherwise we need to insert the data.

But I have 2 categories of updates for which I need the values of both the value of the data being processed daily and the data present in the database to pass across the change capture stage.

Any way I can do this

regards,
varsha

[ray.wurlod]

Two Change Capture stages?

[vmcburney]

CDC is quite straight forward, your delta data is the "After" view and your dimension is the "Before" view. CDC will output your rows with the change data code that will indicate which rows are modified and which are new. Make sure you drop off unchanged rows and delete rows and you are good to go. You can then apply these updates and inserts to the database.

If you want to combine it with the data already in the database then use the Change Apply stage after your Change Capture stage and write your changes to your "Before" dataset. This will give you a combined "Before/After" dataset that can be loaded into an empty table.

Mostly we use Change Capture but not Change Apply since Change Apply works against a dataset and we want to apply changes to an already populated "Before" table.