SAP Table Delta Extract Made Easy through Dynamic Filters

Abstract

For the table based data extraction requirement, it is always highly desired to have incremental loading capability, or delta load. However, tracking and maintaining the delta pointer is an additional overhead. This article explain how AecorSoft Integration Service can support dynamic filters during SAP table extraction by leveraging SSIS variables, without going through the additional custom effort of delta tracking. The same technique can be very useful for partial full load scenario as well.

How It Works

Microsoft SQL Server Integration Services (SSIS) offers the support for variables, which can be referenced during run time. Variables can store values which can be set at design time or updated at run time. Two types of SSIS variables are offered: system variable, and user-defined variable. The user-defined variable is relevant here to help achieve the dynamic filtering objective.

The user-defined variables can be created from within Visual Studio IDE when the SSIS package is being configured.

AecorSoft Integration Service supports SSIS variables. Through the example, we will explain how to easily configure the powerful dynamic filter to perform SAP table delta load.

Example

Let us use SAP Sales Order table VBAK as the example. The idea is to load only newly created records from VBAK, by filtering ERDAT (Create Date) with current system date. In real world we would need to consider the Changed On Date, but here we only use Create Date to explain the functionality.

We can either maintain a separate delta tracking database table which is updated with the current date through a stored procedure and then join VBAK with that delta tracking table, or we can seek a way to pass the current date to the filter condition dynamically when the package is executed.

The Step-by-Step Guide

Create Variable

In Control Flow window, right click anywhere on the Control Flow canvas and select “Variables” from the pop-up menu:

View fullsize

From the Variable window, click the first icon, which is “Add Variable”, on the toolbar:

View fullsize

Give a suitable name. Choose Data Type as “String”, because here we want to get the dynamic date value in “YYYYMMDD” String format and pass it to the filter. In the context of using AecorSoft to filter on SAP fields, we expect all filter values to be in String format.

Next, we plan to write a dynamic script. Therefore, we need to use the Expression feature by clicking on the three dots to bring up the Expression Builder dialog

View fullsize

Create the Expression

The Express Builder is a place for all the creativity. In order to achieve the desired Date format, we use the Date function GETDATE() and combine with other String functions. This is the expression which produces YYYYMMDD:

(DT_WSTR, 4)(DATEPART("yy" , GETDATE())) + RIGHT("0" + (DT_WSTR, 4)DATEPART("mm" , GETDATE()),2) + RIGHT("0" + (DT_WSTR, 4)DATEPART("dd" , GETDATE()),2)

Click on “Evaluate Expression” to validate the result. If everything looks good, click “OK” to accept the expression.

Use the new variable

Now, go to AecorSoft Table component dialog, right click on ERDAT field, select Filter, and enter the filter value as “@[User::var1]”. This is the standard SSIS syntax, meaning it is for user-defined variable with name “var1”.

View fullsize

Now the configuration is done!

Execution

When the package is executed, the expression will be evaluated by SSIs and the actual system date value in YYYYMMDD String format will be passed as filter to get only the filtered records back.

Conclusion

Within minutes, by leveraging the powerful SSIS user variable feature, we can achieve SAP table delta extraction through dynamic filters without stress. Combining this with the world famous AecorSoft Turbo Function, the SAP table extraction experience is better than ever!