{"id":7490,"date":"2019-07-20T10:01:04","date_gmt":"2019-07-20T10:01:04","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=7490"},"modified":"2019-08-11T11:24:40","modified_gmt":"2019-08-11T11:24:40","slug":"import-sap-s-4hana-odata-service-sql-server","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/import-sap-s-4hana-odata-service-sql-server\/","title":{"rendered":"Import SAP S\/4HANA OData Service Data Into Sql Server via ODBC Driver"},"content":{"rendered":"

Introduction<\/span><\/h2>\n

\"SAP<\/a><\/p>\n

In our previous blog we saw how to read JIRA data in SQL Server<\/a>. Now let\u2019s 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\u00a0ODBC XML Driver<\/a>\u00a0to read SAP data and load into SQL Server.<\/p>\n

So, let\u2019s get started.<\/p>\n

\n

<\/h2>\n

<\/h2>\n

Requirements<\/span><\/h2>\n
    \n
  1. A first requirement will be to SQL Server Database Engine Installed<\/li>\n
  2. The second requirement will be SSMS installed<\/li>\n
  3. Finally, make sure to have\u00a0ZappySys ODBC PowerPack<\/a>\u00a0installed.<\/li>\n<\/ol>\n<\/div>\n

    About SAP HANA \/ OData REST API Service<\/span><\/h2>\n

    You can expose your SAP Data using ODATA REST API Service. Here is a\u00a0good article<\/a>\u00a0which shows how to expose data as OData Service.\u00a0 \u00a0For more information on SAP OData Service feature check this\u00a0SAP help page<\/a>. If you are new to OData Standard then\u00a0read here<\/a>\u00a0to 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.<\/p>\n

    https:\/\/services.odata.org\/Northwind\/Northwind.svc\/\r\nhttps:\/\/services.odata.org\/Northwind\/Northwind.svc\/Customers\r\nhttps:\/\/services.odata.org\/Northwind\/Northwind.svc\/Orders\r\nhttps:\/\/services.odata.org\/Northwind\/Northwind.svc\/Invoices\r\nhttps:\/\/services.odata.org\/Northwind\/Northwind.svc\/Products<\/pre>\n
    Now let\u2019s look at how to read SAP Data using ODBC Driver. At this point we assume you have exposed your data as OData Endpoint.<\/p>\n
    Read\u00a0SAP S\/4HANA data using XML Driver<\/span><\/h2>\n
    First let\u2019s 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.<\/p>\n
      \n
    1. To do this, first of all, we will open the ZappySys Data Gateway Configuration:\n
    2. Add the Native – ZappySys XML Driver Data source.\n
    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},\u00a0 {MY-PROJECT}, {MY-TABLE})\n
      https:\/\/{MY-INSTANCE}\/sap\/opu\/odata\/sap\/{MY-PROJECT}\/{MY-TABLE}<\/pre>\n
      For example if you are hosting SAP HANA in Cloud Instance then your URL may look like below<\/p>\n
      https:\/\/myXXXXXX-api.s4hana.ondemand.com\/sap\/opu\/odata\/sap\/MyTestProject\/PurchaseOrders<\/pre>\n
      And create New ZS-HTTP connection in it select\u00a0Basic Authentication<\/a>\u00a0and enter your SAP HANA UserID \/ Password to call OData Service and select the desire filter.<\/p>\n
      \"XML<\/a>
      XML Driver : SAP S\/4HANA OData Service Configuration<\/p><\/div><\/li>\n
    4. Now go to Data Format \/ Compression (Zip\/GZip) tab and select Data Format as OData to get all the records.\n
    5. Finally, now using Query Builder and Code Generator we will generate the query.\n
    6. That\u2019s it we are ready to load SAP S\/4HANA OData Service data to SQL Server.<\/li>\n<\/ol>\n
      Load SAP S\/4HANA OData Service in MS SQL Server<\/h2>\n
      Once you configured the data source in Gateway, we can now setup Linked Server in SQL Server to query API data.\r\n
        \r\n \t
      1. Assuming you have installed SQL Server and SSMS. If not then get both for FREE from here:\u00a0Get SQL Server Express<\/a>\u00a0and\u00a0\u00a0Get SSMS<\/a><\/li>\r\n \t
      2. Open SSMS and connect to SQL Server.<\/li>\r\n \t
      3. Go to Root > Server Objects > Linked Servers node. Right click and click\u00a0New Linked Server...<\/strong>\r\n
        \r\n\r\n\"Add<\/a>\r\n
        Add Linked Server in SQL Server<\/p>\r\n\r\n<\/div><\/li>\r\n \t
      4. \u00a0Now enter the linked server name, select Provider as SQL Native Client<\/li>\r\n \t
      5. Enter data source as\u00a0GatewayServerName, PORT_NUMBER<\/span><\/strong>\u00a0where 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.<\/li>\r\n \t
      6. Enter Catalog Name. This must match name from Data gateway Data sources grid > Name column\r\n
        \r\n\"Configure\r\n<\/a>\r\n
        Configure Linked Server Provider, Catalog, Server, Port for ZappySys Data Gateway Connection<\/p>\r\n<\/div>\r\n
        \r\nINFO:<\/strong>\r\n