My requirement is to set up an integration to between flat file (this could be tab delimited or csv) and CRM 2016 via Scribe.
Due to many limitations the flat file will be a complete extract and not just the delta. So we’ll need to isolate the delta and insert them into CRM 2016 online. All this using scribe. These are the approaches I could think of:
Use the flat file as the source, use target variables/dblookups to get the value for each field in CRM. Then compare this value with the value of the source field and set up steps to update the record only if the field is has changed. [This means we’ll have to process ALL the records]. This will be a timed integration running once every night.
We can use scribe query publisher to get all the unprocessed rows from the source flat file; Each row as an xml message and using a schema file as the source each msg will be processed. From here on it is similar to (1). As a final step in the dts the source flat file will be updated and a value will be set in the record to show its processed. [again all records will have to processed, this will be a bit faster than (1) I believe]
Have 2 instances of the flat file. Say OldFile and NewFile in the same folder. We can create 2 txt dataobjects using these. In scribe publisher these 2 files will behave like a virtual table in either of these dataobjects. From here we can run a sql query to return the delta. (not sure if we can use join here). These delta records can then be processed similar to (2), except this time we already have the delta therefore a lot less records to process. So we can either do a direct insert/update (even do another level of comparison, this time with CRM fields) and then insert/update.
What do you guys think would be better? Also I am open to new ideas.
