Data Services Python Script Call

Data Services Python Script Call

In the magical world of data extraction Data Services offers great out of the box functionality to extract data from databases like SQL Server, MySQL, PostgreSQL, Oracle… and from SAP ECC. However, to extract data from REST APIs it lacks some functionality. Therefore, we will use one of the most currently popular programming/scripting language: Python.

Now we want to combine the best of both worlds, Data Services and Python. However, let us first look at the high-level client setup.

All Python scripts must be called in the same way every time from Data Services. The Python scripts are located on the Data Services server. For each Python script, if needed, we create a virtual environment on the server.

At client side, data mainly comes from databases like SQL Server, MariaDB, PostgreSQL, Oracle or from SAP ECC. For the extraction of this type of data we use SAP Data Services which offers great out of the box functionality to perform this.

The client also wants to have data from social media and other REST APIs. To extract this type of data we will use Python because since Data Services is rather limited to extract these types of data. This Python script will be put on the Data Services server and there we must create a virtual environment.

Via BODS we want to execute Python scripts and we want to do this in a unified way. In Data Services we created a single Workflow which will be called to execute all Python scripts. A Workflow is a reusable object where we can pass parameters. This way we can make it reusable.

There are 3 parameters:

• Project location (on BODS server)
• Script name
• Script parameters

The python script will be executed through a CMD command which is launched on the Data Services server.

print(exec(‘cmd’, ‘cd [$$Python_Script_Location][$P_Project_Location] && mkdir log & cd [$$Python_Script_Location] && cmd_scriptsdatetime.cmd >> [$P_Project_Location]logcmd_logging_[$G_Job_Name]_%DATE:~-4%%DATE:~-7,2%%DATE:~-10,2%.log && cd [$P_Project_Location] && pipenv run python [$P_Python_Script_Name] [$P_Python_Script_Parameters] >> logcmd_logging_[$G_Job_Name]_%DATE:~-4%%DATE:~-7,2%%DATE:~-10,2%.log 2>&1’, 1));

To make it more generic everything is mapped to variables as much as possible.

Also, the CMD logging is written into a log folder which makes it much easier when dealing with errors.

This is most likely not the best solution to execute python script. But given certain limitations at client side we were able to achieve great things this way.