Loading data from e-mail attachments into SAP BW

With the availability of SAP Smart Data Integration there are not many connection problems that cannot be solved directly anymore by SAP BW. But, once in a blue moon you run into a new challenge that you just want to solve! Together with our partner Interdobs, we built something that we really would like to showcase in this blog.

"What if there is no other way of extracting data from a system than sending it via mail? Above that this data needs to be extracted and loaded multiple times a day automatically. Think outside of the box and combine the best of SAP and Azure to come up with a solution!"

In this joint-effort blog, by Interdobs and Sabaas, we will take you on a journey through the magical world of Azure Logic Apps.

Obviously loading data into SAP BW is its main purpose. While in the past flat files were mainly limited to text files (.csv and .txt), with SAP Smart Data Integration we are now able to easily load Microsoft Excel files as well. Even when the file is not directly reachable via AL11, with the new SharePoint connector we have quite a lot of possibilities to pick up files from everywhere. However in this case we came across a system that was only able to send Excel attachments by mail. While thinking of a solution we came up with the idea of looking into Azure Logic Apps.

Azure Logic Apps is a cloud-based platform for creating and running automated workflows that integrate apps, data, services and systems. Basically Azure Logic Apps contains a lot of connectors, scripts, etc. to automate loads of things. To even simplify the concept more: it’s a lot of “when this, do this” components together in one platform.

Azure Storage accounts is where we are going to store our files. The attachment is stored directly from our Logic App into an Azure Fileshare. This Fileshare we than mount on the SAP BW system, enabling the transition from email attachment to a file we can load into BW.

On-premises data gateway can help us with communicating from our Logic App to SAP BW. We will call SAP BAPIs directly from our Logic App once the attachment has been stored.

So, to put our challenge into words: when a new mail arrives to a certain mailbox, we want to check if it has an attachment and if it is correctly named. Then store it as an Azure Fileshare to eventually load it into SAP BW. When breaking this up into blocks, it would look like this:

Our final solution in Azure Logic Apps would look like this (tilted horizontally for visual improvements):

That looks quite similar right? A short explanation of how the flow works component by component:

When a new mail arrives (V3)

The first component constantly monitors a mailbox for incoming mails and when one comes in, it will trigger the flow. The good thing about this component is its ability to monitor a mailbox and arrange the initial shifting of mails with and without attachment.

For each
The first component is followed by a component that makes sure that for every attachment the next steps are done.

Check naming of files
Next up is a component that checks the filename of the attachment and makes sure that the filename stays consistent (i.e. not removing any date components in a name). Since SAP BW is a bit precarious on the naming of files in a data source, it is mandatory that the filename stays consistent.

Get attachment (V2)
This component will “pick-up” the file and its content and store it in such a way that it can be transferred to the next step.

Create File
The next component will create a file from the “pick-up” of the last step on a location that SAP BW can reach.

Call SAP BW BAPI
After all, dark blue boxes in the flow chart are done a BAPI in SAP BW is called.

The BAPI in SAP BW then calls a function module (the famous RSPC_CHAIN_START) with a default process chain that needs to be kicked off (defined per flow).

In the end, to make sure the entire flow works, the process chain will automatically send a mail when the files have successfully been loaded into SAP BW.

By combining Azure Logic, Azure Storage Account Fileshares and the on-premises data gateway we created a cost effective, serverless, resilient solution to load data from emails into SAP BW.