In the past, my go-to approach for ETL jobs has been writing Python scripts (as Python, unlike many other dedicated ETL tools, brings the full power, rich libraries and customisability a programming language), and then execute these scripts using exec xp_cmdshell from within SQL Stored Procedure by:
EXEC xp_cmdshell 'C:/.../python.exe C:\...\script.py';
which was then scheduled via SQL Server Agent. However, this approach requires dedicated managed SQL Server machine on which Python can be installed, which is a different species from the Azure SQL Database (my employer moved to cloud). Since there is no alternative to exec xp_cmdshell on Azure SQL Database, I am wondering if there is a work around.
I know there is Elastic Job Agent on Azure SQL Database, so we can schedule Stored Procedures, but I cannot figure out about the Python part. I have Googled extensively and any help would be appreciated.
P.S. I know Python scripts can be run via Azure Data Factory but that is an additional cost.
