Read SAP S4 / HANA data in SSIS (OData REST API)

Introduction

In out previous post we saw how to call REST API in SSIS. Now let’s learn how to read data from SAP S4 / HANA OData Service (i.e. S/4HANA). SAP HANA provides OData REST API interface to access data in your application using HTTP Protocol. We will use SSIS XML Source component to read SAP data and load into SQL Server Table in few clicks.

About SAP HANA / OData REST API Service

You can expose your SAP Data using ODATA REST API Service. Here is a good article which shows how to expose data as OData Service.   For more information on SAP OData Service feature check this SAP help page. If you are new to OData Standard then read here to know more how OData can facilitate data extraction using HTTP REST API. If you need Sample XML based OData Service then use below test URLs.

Now let’s look at how to read SAP Data in SSIS. At this point we assume you have exposed your data as OData Endpoint.

Step-By-Step

Let’s look at step by step approach on how to read SAP data using SSIS components and then we will load SAP HANA data into SQL Server.

Read SAP HANA data using XML Source

First let’s look at steps to configure XML Source. We will read data from SAP S4 / HANA OData Service and then in next section we will look at how to load data into SQL Server or other target.

1. Open SSIS Package
2. Drag data flow task from SSIS Toolbox, double click data flow
3. From data flow toolbox drag ZS XML Source on to the Data flow designer. Double click it to configure.
4. Change Data format to OData. Selecting this option will take care REST API pagination.
5. Enter your OData Service URL its typically like below. Replace 3 parts with your own value (i.e. replace {MY-INSTANCE},  {MY-PROJECT}, {MY-TABLE} )
   
   https://{MY-INSTANCE}/sap/opu/odata/sap/{MY-PROJECT}/{MY-TABLE}

   1	https://{MY-INSTANCE}/sap/opu/odata/sap/{MY-PROJECT}/{MY-TABLE}

   For example if you are hosting SAP HANA in Cloud Instance then your URL may look like below
   
   https://myXXXXXX-api.s4hana.ondemand.com/sap/opu/odata/sap/MyTestProject/PurchaseOrders

   1	https://myXXXXXX-api.s4hana.ondemand.com/sap/opu/odata/sap/MyTestProject/PurchaseOrders

6. Check Use Credentials and select on New ZS-HTTP connection type
7. On HTTP connection, Select Basic Authentication and enter your SAP HANA UserID / Password to call OData Service
   

   Configure SSIS HTTP Connection (SAP S4 / HANA OData Service – Using Basic Authentication)

8. Back to XML Source you can now click on select filter or directly type $.feed.entry[*]  as below.
   

   Select Filter for OData Service (XML REST API)

9. Click Preview to make sure you can see records.
10. Click OK to Save UI

In the next section, we will configure the target to load SAP data in SQL Server using Upsert Destination.

Load into SAP HANA data into SQL Server using Upsert Destination

Conclusion

In this post we saw how easy it is to read SAP S4 HANA data using SSIS XML Source. You can download SSIS PowerPack from here and try out yourself ( including many options and 70+ components not discussed in this post).