Problems with Executables created via PyInstaller running from Task Scheduler or as SQL Agent Jobs

[Andrew Lindner]

Hello All,

Python version: 3.6.6

PyInstaller Version: 3.5

I'm running on Windows 10, and windows server 2016

I've used PyInstaller historically to just create simple exe files for others to use my python data automation scripts. I now have a script that needs to run on a schedule for getting data moved into a SQL server. However, no matter what method I'm using to launch the exe file in the background as a sysadmin user without a user interface, the process just hangs. I've attempted simplifying the script down to just writing to a file, checked permissions, attempted structuring it as a batch file, and tried a few different PyInstaller options, to no avail. My Stackoverflow post has full info, but I'll paste my code example below.

Stackoverflow post for full information:

https://stackoverflow.com/questions/57812292/hanging-sql-job-step-when-running-python-executable-created-from-pyinstaller

#Quite a few libraries removed
import datetime, csv, os.path, datetime
#Setup Logging here
now=datetime.datetime.now()
logfilename='FBlog_' + str(now.strftime('%Y-%m-%d_%H-%M-%S'))
#print(logfilename) \#removed as part of removing any console visibility
logfb=open('logs/%s.txt' % logfilename, 'w+')
startstring='Starting run ' + str(now.strftime('%Y-%m-%d_%H:%M:%S')) + '\n'
logfb.write(str(startstring))
logfb.close()

##########Application build code for pyinstaller:############
import PyInstaller.__main__
PyInstaller.__main__.run([
    '--name=%s' % 'TestApp',
    '--onefile',
    '--noconsole',
    '--clean',
    'TestApp.py'
])

Recent update:

Most recently, I attempted to remove the code sections accessing files, and made it so that it instead accesses a SQL server and writes to it via pypyodbc. That appears to work. But, anything touching files in the local directory, doesn't work. I've double checked read/write/execute permissions on the directory where the EXE file is deployed. Is there something present in PyInstall applications that would function differently inside of windows when trying to access files as a user without a UI/command prompt?

Thanks for any help/tips for new stuff to try in advance.

[Andrew Lindner]

Discovered fix:
Apparently an issue with the "start in" directory due to the SQL Agent's user context.
Fixed the issue by changing the start directory in the job scheduler and SQL to be in the same directory as the executable.

Moved SQL job step from CmdExec to Powershell and used the following command: Start E:\LoadingFiles\TestApp\TestApp.exe -wo E:\LoadingFiles\TestApp