This chapter shows best practices about the integration and usage of Xtract Universal with Pentaho Data Integration (ETL) aka Kettle by calling the Xtract Universal Command Line (xu.exe).

The picture below shows the architecture. In Xtract Universal, we have defined an extraction with an SQL Server as the destination. In Pentaho, we execute the extraction by calling the command line tool of Xtract universal (xu.exe). The data will then be extracted from SAP and loaded into the SQL Server database. In Pentaho we can then read and process the data from the database table. xu-pdi-push

This scenario assumes that Pentaho runs on a Windows operating system or can call a Windows Command Tool. In the case of other operating systems, the call can alternatively be made via HTTP.
Extraction in Xtract Universal
Here we see the definition of the extraction in Xtract Universal with SQL Server Destination: pdi-cmd-xu


Transformation in PDI
The overview of the transformation in Kettle shows the steps used: pdi-cmd-transf-overview


Initial Parameters
Let’s look at the settings of the important steps. In the first step we define 2 parameters:

  • The field XUCmd contains the path to the command time: “C:\Program Files\XtractUniversal\xu.exe”.
  • The XUArg field contains the URL of the extraction in Xtract Universal: http://KETSWIN16DC02:8065/?name=SAPCustomersToSQL

pdi-cmd-transf-generate-rows-settings


Call Command Line
In the second step we call the command line with the following settings. As you can see, we have also implemented error handling.
pdi-cmd-transf-exe-settings01

pdi-cmd-transf-exe-settings02


Database Connection
Here we see the connection to the SQL Server that we use to read the data from the table: pdi-cmd-transf-sql-server-connection


Table Input
We use the following settings in the Table Input step: pdi-cmd-transf-table-input-settings


Execute the Transformation in PDI
After successful execution we would find the following metrics in PDI. pdi-cmd-transf-metrics


Preview in PDI
It is also possible to preview the individual steps.
Preview of the input fields:
pdi-cmd-transf-generate-rows-preview


Data Preview of the Table Input step: pdi-cmd-transf-table-input-preview


Data in SQL Server
Here you find a preview of the data in the SQL Server: pdi-cmd-sql


Download of the transformation file for PDI
You can download the transformation template for Pentaho Data Integration (PDI) aka Kettle here: Call SAP Extraction from Xract Universal via CMD.ktr