SQL Server Agent not executing part of Python script where it runs a third party exe file

Question

I am trying to run a python script from a powershell script inside SQL Server Agent.

I was able to execute most job of a python script (Task1-Task2) except the last portion (Task3) where it runs a third party exe file called SQBConverter (from RedGate) which converts files from SQB format to BAK format.

When I manually run powershell script directly which runs python script, there is no issue.

I modified the "Log On As" from default ("Local System") to my own (JDoh), and it executes the powershell within SQL Server Agent, but it only does the job except where it converts files from SQB to BAK format (Task3).

Without changing to my own (JDoh), it would not even executes the any job of python script.

I don't think there is any issue with powershell script side because it still triggers python script when I changed the "Log On As" to "Local System". It does not show error, but it shows as SQL Server Agent job completed. But, it does not run any tasks within python script at all.

So, I am guessing it might be something to do with SQL Server Agent not able to trigger/run the SQBConverter exe file.

Here is whole python code (ConvertToBAK.py) to give you the whole idea of logic. It does everything until where it converts from SQB to BAK (Task3: last two lines).

import os
from os import path
import datetime
from datetime import timedelta
import glob
import shutil
import re
import time, sys

today = datetime.date.today()
yesterday = today - timedelta(days = 1)
yesterday = str(yesterday)

nonhyphen_yesterday = yesterday.replace('-','')
revised_yesterday = "LOG_us_xxxx_multi_replica_" + nonhyphen_yesterday 

src = "Z:\\TestPCC\\FTP"
dst = "Z:\\TestPCC\\Yesterday"
password = "Password"
path = "Z:\\TestPCC\\FTP"
now = time.time()

### Task1:  To delete old files (5 days or older)
for f in os.listdir(path):
  f = os.path.join(path, f)
  if os.stat(f).st_mtime < now - 5 * 86400:
    if os.path.isfile(f):
      os.remove(os.path.join(path, f))

filelist = glob.glob(os.path.join(dst, "*"))
for f in filelist:
    os.remove(f)

### Task2: To move all files from one folder to other folder location
src_files = os.listdir(src)
src_files1 = [g for g in os.listdir(src) if re.match(revised_yesterday, g)]

for file_name in src_files1:
    full_file_name = os.path.join(src, file_name)
    if os.path.isfile(full_file_name):
        shutil.copy(full_file_name, dst)   

### Task3: Convert from SQB format to BAK format (running SQBConverter.exe)     
for f in glob.glob(r'Z:\\TestPCC\\Yesterday\\*.SQB'):  
    os.system( f'SQBConverter "{f}" "{f[:-4]}.bak" {password}' )

This is powershell code (Test.ps1):

$path = 'Z:\TestPCC'
$file = 'ConvertToBAK.py'

$cmd = $path+"\\"+$file  # This line of code will create the concatenate the path and file
Start-Process $cmd  # This line will execute the cmd 

This is screenshot of SQL Server Agent's step:

I looked at the properties of SQBConverter exe file itself, and I granted FULL control for all users listed.

Answer 1

I got it working by modifying the last line of my Python code.

From:

os.system( f'SQBConverter "{f}" "{f[:-4]}.bak" {password}' )

To (absolute path):

os.system( f'Z:\\TestPCC\\SQBConverter.exe "{f}" "{f[:-4]}.bak" {password}' )