NDSO delta identification without having RecordMode in source files

[dimitris_emmanoulopoulos2]

Hi,

I have versions of a text file containing Sales Orders.

The first one is yesterday's data and the second is yesterday's+today's data.

So the text files look like:

1st file (yesterday's data):

SalesOrderID;OrderDate;CustomerName;ProductName;OrderQuantity;Sales

1;13.10.2010;Muhammed MacIntyre;Eldon Base for stackable storage shelf, platinum;6;261.54

2;01.10.2012;Barry French;Cardinal Slant-D® Ring Binder, Heavy Gauge Vinyl;27;244.57

3;10.07.2011;Clay Rozendal;R380;30;4965.76

4;28.08.2010;Carlos Soltero;Holmes HEPA Air Purifier;19;394.27

2nd file (yesterday's+today's data):

SalesOrderID;OrderDate;CustomerName;ProductName;OrderQuantity;Sales

1;13.10.2010;Muhammed MacIntyre;Eldon Base for stackable storage shelf, platinum;7;261.54

2;01.10.2012;Barry French;Cardinal Slant-D® Ring Binder, Heavy Gauge Vinyl;27;244.57

4;28.08.2010;Carlos Soltero;Holmes HEPA Air Purifier;19;394.27

5;29.08.2010;C Solt;Holmes HEPA Air Purifier;19;394.27

So the second file contains 3 records existing in the first file (SalesOrderID 1,2,4), 1 record missing from first file (SalesOrderID 3) which must be deleted and 1 new (SalesOrderID 5)

Also the OrderQuantity in record 1 was changed from 6 to 7. So this record must be updated.

I do not have a RecordMode that can define if the record is new or to update or to delete because the source system that exports these text files doesn't have this capability to define the RecordMode.

Is there a way that NDSO can identify the changes between Day 1 & Day 2 data and do the proper actions (update 1 delete 3 and insert 5)?

Regards,

Dimitris

[Sefan_Linders]

In that case you should set the recordMode to a fixed value of ‘N’ . The NDSO or flowgraph cannot see changes in the flat file before doing a full comparison. The entire data set would be loaded into the inbound queue, but during activation the NDSO will check which record has changed and which not. Based on that the changelog table is populated, and subsequent targets will be fed only with the changes. Note that deletions are not recognized. If that is required, you will have to use the flowgraph table compare transform.

Edit: for deletions, you can also try the snapshot option in the NDSO. Still, the entire flat file would be loaded into the inbound queue, but differences between inbound and active table would then also include deletes.

If you just want the flat file to show up 1 to 1 in a target table, you might as well use a truncate plus load. The benefit of the NDSO would be providing history in the changelog, the option to roll back, and loading only deltas to the following target. That all with minimal extra data storage and an efficient compare mechanism.

[dimitris_emmanoulopoulos2]

Sefan,

Thank you for your answer. I will try this and I hope it will do the job.

Regards,

Dimitris