Import SAP S/4HANA OData Service Data Into Sql Server via ODBC Driver



In our previous blog we saw how to read JIRA data in SQL Server. Now let’s learn how to Import SAP S/4HANA OData Service Data Into Sql Server. SAP S/4HANA provides OData REST API interface to access data in your application using HTTP Protocol. We will use ODBC XML Driver to read SAP data and load into SQL Server.

So, let’s get started.


  1. A first requirement will be to SQL Server Database Engine Installed
  2. The second requirement will be SSMS installed
  3. Finally, make sure to have ZappySys ODBC PowerPack installed.

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 using ODBC Driver. At this point we assume you have exposed your data as OData Endpoint.

Read SAP S/4HANA data using XML Driver

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

  1. To do this, first of all, we will open the ZappySys Data Gateway Configuration:
    Open ZappySys Data Gateway

    Open ZappySys Data Gateway

  2. Add the Native – ZappySys XML Driver Data source.
    Add Gateway Data Source

    Add Gateway Data Source

  3. Now edit that XML data source to configure it. 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})
    For example if you are hosting SAP HANA in Cloud Instance then your URL may look like below
    And create New ZS-HTTP connection in it select Basic Authentication and enter your SAP HANA UserID / Password to call OData Service and select the desire filter.

    XML Driver : SAP S/4HANA OData Service Configuration

    XML Driver : SAP S/4HANA OData Service Configuration

  4. Now go to Data Format / Compression (Zip/GZip) tab and select Data Format as OData to get all the records.
    Data Format : Odata

    Data Format : Odata

  5. Finally, now using Query Builder and Code Generator we will generate the query.

    Generate Query

  6. That’s it we are ready to load SAP S/4HANA OData Service data to SQL Server.

Load SAP S/4HANA OData Service in MS SQL Server

Once you configured the data source in Gateway, we can now setup Linked Server in SQL Server to query API data.
  1. Assuming you have installed SQL Server and SSMS. If not then get both for FREE from here: Get SQL Server Express and  Get SSMS
  2. Open SSMS and connect to SQL Server.
  3. Go to Root > Server Objects > Linked Servers node. Right click and click New Linked Server...
    Add Linked Server in SQL Server

    Add Linked Server in SQL Server

  4.  Now enter the linked server name, select Provider as SQL Native Client
  5. Enter data source as GatewayServerName, PORT_NUMBER where server name is where ZappySys Gateway is running (Can be same as SQL Server machine or remote machine). Default PORT_NUMBER is 5000 but confirm on Data gateway > General tab in case its different.
  6. Enter Catalog Name. This must match name from Data gateway Data sources grid > Name column
    Configure Linked Server Provider, Catalog, Server, Port for ZappySys Data Gateway Connection

    Configure Linked Server Provider, Catalog, Server, Port for ZappySys Data Gateway Connection

  7. Click on Security Tab and select last option "Be made using this security context". Enter your gateway user account here.
  8. Click OK to save Linked Server
  9. In SSMS execute below SQL query to test your connectivity.
  10. Here is the preview after you run some REST API query in SQL Server. Notice that you can override default configuration by supplying many parameters in WITH clause (second query example in the screenshot).

    SSMS Output - Query REST API via Linked Server OPENQUERY statement (Connect to ZappySys Data Gateway)

  11. You can wrap your queries inside View or wrap inside Stored procedure to parameterize. Here is an example of creating the view which calls REST API queries.
    CREATE VIEW dbo.vw_MyAPICall_View 
    /*Call REST API inside SQL Server View*/
  12. Notice in above approach if you parameterize Stored Procedure then check this article to understand Dynamic Metadata.
  13. Now let's insert API data into the new data table "tblMyAPiData" in the SQL server database. For that, we need to execute below SQL query.

    Insert data into the new table

  14. Let's insert and update records into the already created table "tblMyAPiData"
    • To do that first we need to insert the new API data into the "#temp_tblMyAPiData" temporary database table.
    • Now let's delete the old records from the database table which are updated.
    • At the last insert all new API data into the database table.
    • To do that, we need to query like below SQL queries.
    DELETE FROM [dbo].[tblMyAPiData] WHERE id in (SELECT id FROM #temp_tblMyAPiData)
    INSERT INTO tblMyAPiData
    SELECT * FROM #temp_tblMyAPiData
  15. In the upper step, we see how to insert and update new records. Now if you want to update particular fields records only then you need to query like this.
    Update dbtbl_1
    set dbtbl_1.field1 = dbtbl_2.field1,
        dbtbl_1.field2 = dbtbl_2.field2
    FROM tblMyAPiData dbtbl_1
    *NOTE: If you are getting error like : "Cannot resolve the collation conflict between “Latin1_General_CI_AI” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation" then you need to query like this :
    Update dbtbl_1
    set dbtbl_1.field1 = dbtbl_2.field1,
        dbtbl_1.field2 = dbtbl_2.field2
    FROM tblMyAPiData dbtbl_1
    COLLATE SQL_Latin1_General_CP1_CI_AS

SAP S/4HANA Integration with Other BI Tools (Power BI, Excel, SSRS, MS Access…)

ZappySys ODBC Drivers built using ODBC standard which is widely adopted by industry for a long time. Which mean the majority of BI Tools / Database Engines / ETL Tools already there will support native / 3rd party ODBC Drivers. Below is the small list of most popular tools / programming languages our Drivers support. If your tool / programming language doesn't appear in the below list, which means we have not documented use case but as long as your tool supports ODBC Standard, our drivers should work fine.   ZappySys ODBC Drivers for REST API, JSON, XML - Integrate with Power BI, Tableau, QlikView, QlikSense, Informatica PowerCenter, Excel, SQL Server, SSIS, SSAS, SSRS, Visual Studio / WinForm / WCF, Python, C#,, PHP. PowerShell

BI / Reporting Tools Integration

ETL Tools Integration Programming Languages Integration


So in this blog, we learned how to Import SAP S/4HANA OData Service Data in MS SQL Server using ODBC XML / SOAP API Driver in a very simple way. You can achieve many more functionalities with this tool. Check our blogs/articles on XML File / REST API Driver to find out what this tool is capable of more.


Finally, you can use the following links for more information about the use of SAP S/4HANA OData Service with our tools:

Posted in ODBC Gateway, T-SQL (SQL Server), XML File / SOAP API Driver and tagged , , , , , .