Lesson 36. Executing Python From SQL Server Agent

Data pipelines built with Python can be orchestrated by SQL Server Agent. However, since Python is not a Microsoft product, there is no tight integration with SQL Server. They try to sell you on the idea that there is, but the cake is a lie.

If there were truly tight integration between Python and SQL Server, there would be an agent job step type specifically for Python. Since there is not, we’ll have to roll our own.

There are some considerations to be aware of when building pipe managed by SQL Server Agent.

Directory Permissions

You should use a Proxy to run Python just like you would SSIS. The Windows account that is being used for the credential has to be explicitly named in the permissions for every directory used in ETL processing. It cannot be in the permissions as part of a group. It has to be that specific user that is being used by the credential.

Absolute File Paths

When executing from SQL Server Agent, your script actually executes at C:\WINDOWS\system32.

Many scripts in this tutorial use os.getcwd() as the base of file paths. When the script runs this would wind you up file path variable values that made zero sense.

Instead, if you use absolute paths, everything works fine.

Script Execution Reporting

We have to get the Python script to tell SQL Server Agent if and when it stops executing. If we do not create this link, the agent job will execute and exit with a success regardless of what is going on with the Python script.

We get the script to report to SQL Server by doing two things.

1. Implementing error handling with try/catch.

2. Using sys.exit() to report the exit state of the script.

sys.exit()

The exit() function of the sys module takes an integer for an argument. That argument will then be passed back to the job step so Agent can decide if it needs to go to the next step or report an error.

Generally, 0 means the script functioned correctly. A value of 1 means the script encountered an error. You can define values higher than one in concert with try/catch to report specific errors.

#Process existed successfully
sys.exit(0)

CmdExec

For the job step to kick off the script we need to use a type of job step called CmdExec. This step allows you to interact with the OS in the same way you would with the command line.

In the command box of the step are a series of commands written in Window’s batch scripting language. First the script is kicked off using the exact same command you would have used. The rest of it is the agent side of the conversation between it and the Python script.

The batch script takes the parameter passed to it from sys.exit() and performs the appropriate action based on that value.

python C:\Opt\DownloadZipFile\lesson-36-ex-1.py

SET EXITCODE = %ERRORLEVEL% 
IF %EXITCODE% EQ 0 ( 
   REM Script Ran Sucessfully
   EXIT 0
)
IF %EXITCODE% EQ 1 (
    REM Script Error
    EXIT 1
)

Examples

Example #1: Automating An Existing Process

Automating existing manual processes is a frequent task for business analytics professionals. You wrote a script. It does a thing. But you have to keep running it manually and that is getting annoying. Time to beef it up for production and automate it so you can go create cool new things.

Example Setup Step 1: File Placement

This is a complex example that requires some set up to run properly. This this is not a tutorial on SQL Server so I have scripted as much for you as I can. These instructions assume that you have followed the directions in Section VIII.

Take the Opt folder in your lesson folder and copy it as close to the root of one of your drives as you can.

Open lesson-36-ex-1.py and update the value of the variable file_exchange_directory as necessary.

Take the file lesson-36-ex-1.py and place it in \Opt\DownloadZipFile. This example can be found in notebook format, however, the Python script version is what we will actually be running.

This is a modified version of our download zip file script. Since the goal is to highlight automation, all we are going to do is download the zip file. We are not going to unpack it.

Below, you will notice that relative file paths have been replaced with absolute paths, error handling has been implemented, and sys.exit() is reporting the exit state of the script.

import urllib.request
import os
import sys
from pyunpack import Archive

url = 'https://query.data.world/s/vb53nuuux6umwmccbwlajvlzttmz3q'
file_exchange_directory = 'C:\\FileExchange\\'
third_party_directory = 'Data.World\\'
etl_process_directory = 'DownloadZipFile'
inbound_directory = 'In\\'
file_name = 'Eurostat.zip'

destination_file = os.path.join(file_exchange_directory, third_party_directory, etl_process_directory, inbound_directory, file_name)

try:
    with urllib.request.urlopen(url) as source_file:
        with open(destination_file, 'wb') as target_file:
            target_file.write(source_file.read())

except BaseException as e:
    print(e)
    sys.exit(1)

sys.exit(0)

Example Setup Step 2: Set Up The Agent Job

The first order of business is to make sure SQL Server Agent is running. Open SSMS and in the object explorer, find SQL Server Agent at the bottom. The icon should have a green arrow on it. If it has a red X, you will need to troubleshoot the issue. You can do that by coming to the student lounge and asking a question.

Open the file CREATE JOB Download Zip File.sql in SSMS and run it. This will create the job that we need to run to execute the Python script. You can find the job by expanding the Jobs folder.

1. Open the job by double clicking the name.

2. Off to the left in “Select a page”, click Steps.

3. In the job step list, double click “Execute Script”.

4. In the Command window, if necessary, change the path of the Python file.

5. Hit ok on the various windows until you have completely exited the job.

Example Setup Step 3: Run The Job

It looks like we are good to go.

LET’S LIGHT THIS CANDLE!

Right click on the job and select “Start Job At Step”.

Once the job has completed, go check the In folder for the application output.