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?

Please post an [mcve]. It's likely the foo.py script is running in your current working directory, and it only knows the file name, not the path. You could try `cd %~p0` in your batch script, before executing the python script. — jwdonahue, Apr 02 '20 at 23:24
You know you answered your own question, right? *"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."* In other words, the SQL Agent job needs to change directory to where the script is before it executes it. — AlwaysLearning, Apr 03 '20 at 03:17
@jwdonahue, this is part of a complex NLP process so even a minimal example that could be reproduced is out of the question. However, you did give me an idea. I'll try your approach. If that fails, I'll just use an absolute path. — Bob Wakefield, Apr 03 '20 at 05:03
@AlwaysLearning I did try to change the directory in the batch script. It still didn't work which is when I became even more confused than I already was. When you run something in the IDE, you're running it in the folder where the file resides. So I don't get where exactly Agent IS running the script. — Bob Wakefield, Apr 03 '20 at 05:06
Can you write a log file from the script? You can dump the entire environment and the cwd. Might give you some clues. I don't anything about how that agent works, but it's possible that presents a very limited view of the file system, to your script. You may have to "install" your files or add some paths to its configuration, somehow. Other than that, I am fresh out of ideas. — jwdonahue, Apr 03 '20 at 19:05

score 1 · Accepted Answer · answered Jun 29 '20 at 21:33

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
)

This wound up being the answer with some subtle caveats: 1. It was a permissions issue. I'm using a Proxy. The credential uses my login. I'm an administrator. Administrators had full control of the target folder but I still had to add my specific user to the folder permissions. 2. py won't work. You have to actually use python in the first line. — Bob Wakefield, Oct 22 '20 at 03:54
Well py is just what I have as an alias it would have to be whatever you have on your system. — JustDave, Oct 22 '20 at 08:30

score 0 · Answer 2 · answered Apr 05 '20 at 01:07

0

This is not THE answer but I'm going to post what I did for now. This was for a client on a deadline so I just slapped something together. I'll post a much more in depth analysis in a few weeks.

The first thing was I needed to have an absolute path to model.joblib. That's my bad. I normally work in Jupyter Lab and forgot that was necessary.

However, that didn't fully fix the problem. The job ran but it was like it was skipping over executing the script which was weird. The final solution was to use an Execute Process Task in SSIS to run the script, then change the agent job from CmdExec to SSIS package and that made everything work right.

answered Apr 05 '20 at 01:07

Bob Wakefield

3,739
4
20
30

Well you do you, I don't have problems running from cmd in sql server job. This might not apply to you specific problem, but it covers getting the correct fail/succeed and error message within sql job. In my case it's to avoid SSIS to begin with and fire off a py script. – JustDave Aug 19 '20 at 15:32
It sounds like permissions to me if you can run it fine but the agent couldn't. – JustDave Aug 19 '20 at 15:40

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

2 Answers2