This article demonstrates, how to implement external web services call in SAP Data Services.

In this particular example, we will try to call an External Web Service by passing two currency codes as arguments and will capture the currency conversion rate as returned by the web service function. So lets get started. The web service accepts a particular XML format for the input parameters and also returns the value as XML message type.

To implement in SAP Data Services first of all we need to create a Web Service type of Datastore. Next we need to Import the required function module. Lastly we will build a Job and a Dataflow to consume the function module.

First of all we need to create the Datastore. Go to Project -> New -> Datastore.
Alternatively, Go to the Datastores tab of the Local Object Library. Right-click and select New to create a Datastore.

Create New DataStore

Lets name the Datastore as DS_WebService. Select the Datastore type as Web Service from the drop down selection list. Next enter the web service information in the Web Service URL section of the Web Service Datastore.

DataStore Type- Web Service

Here for this demonstration we are using the below Currency Conversion web service.
http://www.webservicex.net/CurrencyConvertor.asmx?WSDL

Next from the Local Object Library, right-click the Datastore and select Open, to explore the External Metadata. Alternatively, double-click the Datastore to open the Datastore Explorer

Open DataStore- External Metadata

Next from the External Metadata, right-click the function module and select Import. This will basically save the external information as Data Services Repository Metadata.

Import Function

So now we can find the Imported function under the Functions section of the Web Service Datastore of the Local Object Library.

Web Service Function

Next double-click the function module to preview the Schema Definition. As mentioned previously this function module expects an input XML Schema as REQUEST and also returns an output XML Schema as REPLY or response.

Web Service Function Schema Definition

So we are good to design a Data Services Job and Dataflow to consume the function call. Below is the implementation screenshot of the Dataflow.

Sample Dataflow to call Web Services

In this example we have a Source Table having a pair of columns FROMCURRENCY and TOCURRENCY. Here the requirement is to capture these currency types and the currency conversion rate to an output table.

Source Table Definition

Next we take a Row Generation Transform in the Dataflow. Keep the properties as default. We are using this to generate a single row for the Header tag of the XML schema that we are going to create. Next we will pass this XML schema information to the Web Service Function and will receive the XML Reply message as the return from the function module.

Row Generation Transform- Properties

Now we will generate a XML Schema as per the required input template for the Web Service Function. So lets take a Query Transform. Next Join the Source Table, namely CONVERT_CURRENCY and the Row Generation Transform to the Query Transform. Next double-click the Query Transform to open the Query Editor to design the Schema Out.

Select the Parent Schema of the Schema Out, here it is Query_Set_Schema. Go to the FROM tab and Check the Input Schema i.e. the Source Table only, here it is CONVERT_CURRENCY. Next from the Schema In select the 3 columns of the Source Table and select Map to Output. Here the purpose is to pass through these Source Table columns to the Target Table.

Query Transform

Next in the Schema Out, right-click and select New Output Schema and provide a Schema Name; here the New Output Schema Name is CURRENCY_RATE. We will be passing this Schema definition to the Web Service Function. Next right-click the New Output Schema and select Make Current. Next go to the FROM tab and Check the Input Schema i.e. the Row Generation Transform only, here it is Row_Generation. Next from the Schema In select the columns FROMCURRENCY and TOCURRENCY of the Source Table and select Map to Output. Now our XML Schema as required by the Web Service Function is ready to be used.

Query Transform- New Output Schema (Make Current)

Next we take another Query Transform in the Dataflow to call the Web Service Function. Map the pass through Source Columns to the Schema Out. Next in Schema-Out right-click and select New Function Call.

Query Transform- New Function Call

Next Select the Web Service Function. So select the Web Service Datastore as appears on the Function categories. Next select the Web Service Function Name.

Select Web Service Function

Now we need to pass the generated XML schema as input arguments to the Web Service Function. So select the Schema from the Input schema of query here CURRENCY_RATE and drag-drop to the textbox below i.e. mapped to the ConversionRate of the Input Schema of the Function Module.

Define Input Schema of function

Since the Web Service Function sends the REPLY in the form of XML schema; so in order to flatten the Hierarchy Schema and load in a Target Table we will use another Query Transform to UNNEST the Return Schema with the Parent Schema. So right-click the Sub-Schema in the Schema Out namely ConversionRateResponse and select UNNEST.

Query Transform- Make Current

Query Transform- Unnest

We are finally good to map the Query transform to the Target Table.

Target Table Definition

Below is the sample screenshot of the Source and Target Data Preview of this demo.

Source-Target Data Preview


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.

  • Handling XML source files in SAP Data Services

    This article will demonstrate how to read data from XML based source files using SAP Data Services. Here our objective is to load employee and department information respectively from the source XML based file.

  • Real Time Jobs in Data Services

    SAP Data Services provides Realtime Data Integration features. Data Services can generate realtime event messages and can also listen to input XML messages. Real Time Job means, some message with content will trigger the Job or Dataflow processing. A RealTime...

  • How to use Data Services Reverse Pivot Transformation

    In this article, we will learn how to use SAP Data Services Reverse Pivot Transform. The Reverse Pivot transformation combines data from several rows into one row by creating new columns. For each unique value in a pivot axis column and each...

  • Fools Guide to BODS - Registering Repository to CMC

    In our earlier article, we have learnt how to create a repository in BODS. Once that part is done, The final process before launching the BODS application is registering the repository with...

  • Data Services Scenario Questions Part 7

    In this tutorial we will discuss some scenario based questions and their solutions using SAP Data Services. This article is meant mainly for Data Services beginners.

  • How to install SAP BODS - Standalone

    This article is a step by step guide on how to install standalone SAP BODS Batch Job Server, Client Components, Web based Administrative Console and how to configure the same.

  • XML file generation using SAP Data Services

    This article will demonstrate how to generate XML target files using SAP Data Services. Here our objective is to generate XML file with employee and department information.

  • SAP Data Services Analytic Functions

    In this tutorial we will learn how to implement Cumulative Sum in SAP Data Services (BODS). Since there is no available in-built Analytic Functions in SAP Data Services, we will achieve the end result by exploiting some in-built Data Services...

  • One Stop to SAP BODI/BODS

    BODI Business Objects Data Integrator or BODS Business Objects Data Services is a GUI workspace that allows to create jobs that extracts data from heterogeneous sources, transforms that data using built-in transforms and functions to meet business...

  • SAP BODS Cluster Installation

    This article is a step by step guide on how to configure SAP BODS for High Availability using Windows Cluster services. To take advantage of fail-over support for SAP BusinessObjects Data Services services in a Windows Clustering Environment,...