With the latest release of AecorSoft Integration Service, doing SAP table-based delta extraction is easier than ever. This article uses SAP Sales Order header table VBAK as an example to explain how to conduct the elegant and powerful way to manage delta extraction with ease.
In most cases, we rely on timestamp fields to identify delta records. Being one of the most important transactional table in SAP, the Sales Order header table VBAK has a couple of important fields:
|ERDAT||Date on Which Record Was Created|
There are two delta scenarios:
For newly created record, ERDAT has value but AEDAT would be empty;
For changed record, AEDAT is updated while its ERDAT remains as is.
Therefore, the high-level delta logic would be “get all new records as well as changed records from last delta run”, which can be further translated into the pseudo WHERE clause:
|WHERE ( ERDAT > = last_run_date AND AEDAT is empty ) OR AEDAT >= last_run_date|
Practically, the “last_run_date” can be specified as “current date minus 1 day”, to ensure all delta records generated since yesterday’s run would not be missed. Inevitably there would be same records which have been extracted in last run, but a MERGE on the destination database would take care of the duplicated extracts.
In order to get yesterday’s date dynamically at run time, we can leverage SSIS expression to build a date value in the YYYYMMDD format which SAP expects:
|(DT_WSTR, 4)(DATEPART("yy" , DATEADD("day", -1, GETDATE()))) + RIGHT("0" + (DT_WSTR, 4)DATEPART("mm" ,DATEADD("day", -1, GETDATE()) ),2) + RIGHT("0" + (DT_WSTR, 4)DATEPART("dd" , DATEADD("day", -1, GETDATE()) ),2)|
We will then define a STRING type SSIS user variable var5 and assign this expression to it:
Next, change the Table filter to Expert Mode:
Then, enter the clause:
|( AEDAT = '00000000' AND ERDAT >= '@[User::var5]' ) OR AEDAT >= '@[User::var5]'|
Leveraging the dynamic filters, we can implement a complete sales order delta extraction logic easily. The same pattern can be expanded into delta extraction scenario when TIMS fields are available to fine control the delta data volume. Even without TIMS field, normally it is still acceptable to have delta data volume within a day’s range if we filter new and changed records based upon Today minus 1, and then rely on database MERGE to get the final image of data in near real time.