Running Python scripts from SQL Server Agent: CmdExec steps, virtual environments and exit codes

#When this makes sense

Small operational scripts that sit next to the database: calling an API and loading the result, generating a file, checking something and alerting. For bigger pipelines, use a real orchestrator. This is not the same as Machine Learning Services (sp_execute_external_script), which runs Python inside SQL Server.

#1. Install Python for all users and create a venv

Install Python for all users (for example C:\Python312), then create a project folder and a virtual environment that belongs to the job:

New-Item -ItemType Directory D:\Jobs\api_loader
& C:\Python312\python.exe -m venv D:\Jobs\api_loader\.venv
D:\Jobs\api_loader\.venv\Scripts\python.exe -m pip install -r D:\Jobs\api_loader\requirements.txt

The venv pins library versions per job, so upgrading one script doesn't break another.

#2. Make the script job-friendly

import sys, logging, argparse
import pyodbc

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s", stream=sys.stdout)

def main(run_date: str) -> int:
    conn = pyodbc.connect("Driver={ODBC Driver 18 for SQL Server};Server=localhost;"
                          "Database=Staging;Trusted_Connection=yes;Encrypt=yes;TrustServerCertificate=yes")
    logging.info("Loading data for %s", run_date)
    # ... work ...
    return 0

if __name__ == "__main__":
    p = argparse.ArgumentParser()
    p.add_argument("--run-date", required=True)
    args = p.parse_args()
    try:
        sys.exit(main(args.run_date))
    except Exception:
        logging.exception("Job failed")
        sys.exit(1)        # non-zero exit = Agent marks the step failed

Key points: log to stdout (Agent captures it), use Windows authentication, and exit non-zero on failure.

#3. The CmdExec step

EXEC msdb.dbo.sp_add_jobstep @job_name = N'API - Load Rates', @step_name = N'Run loader',
     @subsystem = N'CmdExec',
     @command = N'"D:\Jobs\api_loader\.venv\Scripts\python.exe" "D:\Jobs\api_loader\load.py" --run-date $(ESCAPE_NONE(STRTDT))',
     @cmdexec_success_code = 0,
     @proxy_name = N'Python Jobs',
     @output_file_name = N'D:\SQLAgentLogs\api_loader_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt';

• Use the venv's python.exe directly. Don't "activate" the venv.
• @cmdexec_success_code = 0 means any other exit code fails the step.
• STRTDT gives the job start date as YYYYMMDD.

#4. Run it under a proxy

Create a credential and proxy for a domain account with only the rights the script needs, and grant it the CmdExec subsystem (sp_grant_proxy_to_subsystem @subsystem_name = N'CmdExec'). That account needs read/execute on D:\Jobs, write on the log folder and a database login.

#5. Secrets

Don't put API keys in the step command or in the script. Options:

• Windows Credential Manager for the proxy account, read with the keyring library.
• An environment-specific config file readable only by the proxy account.
• A secrets vault the server can reach.

#Troubleshooting

Tip

Test as the proxy account with runas /user:CONTOSO\svc-python cmd, then run the exact step command. That reproduces what Agent does.

#Checklist

• venv per job with pinned requirements
• Script logs to stdout, exits non-zero on error
• CmdExec step with success code 0 and output file
• Proxy account with least privilege
• No secrets in commands