Why does my Python script fail when I run it from SQL Server Agent?

Question

I have a python script that works fine when I run it in an IDE. If I execute it from a command line, I have to be in the directory in which is resides in order for it to run properly. If I try to run it as an agent job or with an Execute Process Task in SSIS it fails.

The script inside the agent job looks like this:

py E:\Opt\AppDirectory\foo.py
SET EXITCODE = %ERRORLEVEL% 
IF %EXITCODE% EQ 0 ( 
   REM Script Ran Sucessfully
   EXIT 0
)
IF %EXITCODE% EQ 1 (
    REM Script Error
    EXIT 1
)

When I run this, or in SSIS, I get:

Traceback (most recent call last):
  File "E:\Opt\AppDirectory\foo.py", line 76, in <module>
    encoder = jl.load('model.joblib')
  File "C:\ProgramData\Anaconda3\lib\site-packages\joblib\numpy_pickle.py", line
 590, in load
    with open(filename, 'rb') as f:
FileNotFoundError: [Errno 2] No such file or directory: 'model.joblib'

model.joblib lives in the exact same directory as foo.py. It's really weird when it says it can't find the file, but I'm staring right at it.

The job can find foo.py. Why can't it seem to find model.joblib?

Answer 1

When calling from SQL SERVER like you have you need to have sys.exit(0) as the last thing to do. I've ran into this so many times where the script executes fine but fails from SQL server agent.

My work around has always been to wrap XYZ in a function. In the below you will get a divide by zero to show sql server will give you the error message. If you remove that error on purpose you will see SQL server succeed.

import sys

def test():
    try:
    
        x = 1/0
    
        if 1 > 0:
            return

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

test()
sys.exit(0)

Go ahead and check it out it should work with your:

py E:\Opt\AppDirectory\foo.py
SET EXITCODE = %ERRORLEVEL% 
IF %EXITCODE% EQ 0 ( 
   REM Script Ran Sucessfully
   EXIT 0
)
IF %EXITCODE% EQ 1 (
    REM Script Error
    EXIT 1
)