Skip to Content

Databases like SQL Server,Oracle etc has CDC feature to enable tracking changed/inserted/deleted records. However, there are situation where we might need to implement the same using a flat file source.

For the purpose of this blog, I am going to consider an example of product master data that comes as flat file and loaded through data services.

Outline:

1) Load the initial data normally as you would do and dump it in a physical table.

2) Create separate data flow to handle the delta.

3) Use table comparison transform to identify the inserted/updated/deleted records with the target table.

4) Use map operation transform to filter out the records based out of operation type flag that is coming out of table comparison transform.

Below is the sample flow:

C1.png

Configuration for Table Comparison transform

/wp-content/uploads/2014/11/c2_579472.png

Configuration for Map Operation – Filter out only records that needs to be inserted

/wp-content/uploads/2014/11/c3_579581.png

Similarly, filter for records that needs to be updated

/wp-content/uploads/2014/11/c4_579582.png

Mapping fields in Map Operation transform

/wp-content/uploads/2014/11/c5_579583.png

Attached atl file and sample data used for loading.

Below video demonstrates working example of flat file based CDC using table comparison and map operation transforms.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply