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.
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:
Transformation in PDI #
The overview of the transformation in Kettle shows the steps used:
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
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.
Database Connection #
Here we see the connection to the SQL Server that we use to read the data from the table:
Table Input #
We use the following settings in the Table Input step:
Execute the Transformation in PDI #
After successful execution we would find the following metrics in PDI.
Preview in PDI #
It is also possible to preview the individual steps.
Preview of the input fields:
Data Preview of the Table Input step:
Data in SQL Server #
Here you find a preview of the data in the SQL Server:
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