In the following we demonstrate the Talend integration via a REST web service call. In Talend we execute an extraction via an HTTP Request.

The following image depicts the architecture. Xtract Universal extracts the data from SAP and delivers it via REST web service in a csv format. Json format would also be another possible option.

We can subsequently process the data in Talend, e.g., write the extracted data into a database.

talend-http-architecture


Job Overview in Talend

The job overview in Talend shows the used steps. There are five steps that are described in detail:

talend-00-job-overview

Extraction in Xtract Universal

In the following we can see the definition of the extraction in Xtract Universal. We extract customer data from the SAP table KNA1:

talend-xu-extraction-def

In Xtract Universal we have defined an extraction with web service in csv format as a destination (HTTP-CSV).
The destination settings for HTTP-CSV offer options to deliver data without a column name and a line separator after the last line. We chose this option in the presented example.
talend-xu-extraction

SQL Server

We created a target table on the SQL server with the following SQL command. The created table is used to load the data later on.

CREATE TABLE [dbo].[customers](
	[SAP_Client_MANDT] [nvarchar](3) NULL,
	[Customer_KUNNR] [nvarchar](10) NULL,
	[Country_Customer_LAND1] [nvarchar](3) NULL,
	[Country_Name_NAME1] [nvarchar](35) NULL,
	[City_Customer_ORT01] [nvarchar](35) NULL,
	[Region_Customer_REGIO] [nvarchar](3) NULL,
	[Account_Group_Customer_KTOKD] [nvarchar](4) NULL
) ON [PRIMARY]

Created table on the SQL Server:
talend-mssql-schema

Xtract Universal also offers a web service for the metadata (i.e. column names and data types) of an extraction. Check the online help for further information.

Talend

Before defining the steps in Talend, we defined a connection to the SQL server (under Meta data > DB Connections) and the table schema.

talend-db-connection-01
talend-db-connection-02

Table Schema:

talend-db-schema

Overview of the meta data definition:

talend-db-metadata

Checking the Talend Job

Now we can check the settings of the five steps in the Talend job.

Step 1 - HTTP Request

In the fist step we execute the HTTP Request.The URI parameter is passed on: http://chaplin:8065/?name=customers_http.

talend-01-httprequest

Step 2 - Line Splitting

The data is split into lines using the line break symbols “\r\n”.

talend-02-normalize

Step 3 - Column Splitting

Data sets are split into columns using the regular expression: \\s*,\\s*(?=(?:(?:[^\"]*\"){2})*[^\"]*$)
We use the previously defined schema for the columns.

talend-03-extract-delimited-fields

Step 4 - Writing into the Database

Now we can write the data into the SQL table. To do so, we use the connection and the schema that we defined in the beginning:

talend-04-dboutput

Step 5 - Logging (optional)

In the following optional step we use a Java component to log the number of the processed data sets in case of a successful operation.
talend-05-print-message

Job Execution

Now we can execute the job. The job is successful and the customer data sets are extracted from SAP and are written to the SQL server.
talend-http-job-executed

Checking the Result on the SQL Server

Now we can check the result on the SQL server.
talend-mssql-result


In the presented article we depicted how one can call SAP extractions provided by Xtract Universal via HTTP and further process them using Talend.