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 minimal reproducible example. 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
– jwdonahue, Commented Apr 2, 2020 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
– AlwaysLearning, Commented Apr 3, 2020 at 3: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
– Bob Wakefield, Commented Apr 3, 2020 at 5: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
– Bob Wakefield, Commented Apr 3, 2020 at 5: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
– jwdonahue, Commented Apr 3, 2020 at 19:05

JustDave · Accepted Answer · 2020-06-29 21:33:13Z

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
)

answered Jun 29, 2020 at 21:33

JustDave

5461 gold badge8 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bob Wakefield Over a year ago

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.

JustDave Over a year ago

Well py is just what I have as an alias it would have to be whatever you have on your system.

Bob Wakefield · Accepted Answer · 2020-04-05 01:07:52Z

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 5, 2020 at 1:07

Bob Wakefield

4,0194 gold badges23 silver badges31 bronze badges

2 Comments

JustDave Over a year ago

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 Over a year ago

It sounds like permissions to me if you can run it fine but the agent couldn't.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related