Nightly Job net change options

[Don Panek]

I have a requirement for a nightly job consuming a flat file of data. The file cannot and does not contain any timestamp or flag indicating changes. Does anyone know of a way to determine if anything changed without just processing the entire file each time Can I generate a HASH on the entire row

[Jeff Levetin]

Hi @Don Panek,

 

There are a couple options:

 

Option 1 - Move the file after the map runs (preferred):

Configure the integration to move the file to another directory after post processing. This is done in the connection properties for the Text File as a Source connection. See the section Post Processing Rule in: https://help.scribesoft.com/scribe/en/#sol/conn/text_entities_tab.htm

 

Option 2 - store SHA1 hash in 3rd party database:

 

You can use the Scribe Labs - Tools connector to generate a SHA1 hash of the data. Use the Execute block, entity SHA1, field InputString. CONCATENATE() all fields.

You'll need to store this in a 3rd party database. You might be able to use a LookupTable through the Scribe Platform API connector. Beware of API usage and table size. The would probably only work with a small data set in the range of thousands.

For each record, hash the data, lookup the hash value in the datastore. If found, skip, else process the record and add the hash it to the datastore.

 

 

Hope this helps.

[Manoj Chaurasia]

If you can use a staging database...first import the CSV files into a database table, with a BatchNum and BatchDate column. Before you run each daily solution, use a Native Query block to update the previous records BatchNum to equal BatchNum+1. Then insert today's CSV file as BatchNum=1, with a BatchDate=GETDATETIME(). Then you can use an EXCEPT query in a SQL Native Query block to compare records in Batch 2 to Batch 1 to get the difference. Then only do something with the records that are different.