SQL Agent History says "To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report"
When I open the [All Executions] report, this is the full error:
Message Type: OnError Message: TEST:Error: In Executing "C:\Program Files (x86)\Microsoft Visual Studio...\python.exe" "\\Server "B"\r$\...\Scripts\Python\TEST.py" at "C:\Program Files (x86)\Microsoft Visual Studio...\DLLs", The process exit code was "1" while the expected was "0".
Same error, different path: Message: TEST:Error: In Executing "C:\Program Files (x86)\Microsoft Visual Studio...\python.exe" "\\Server "B"\R\...\Scripts\Python\TEST.py" at "C:\Program Files (x86)\Microsoft Visual Studio...\DLLs", The process exit code was "1" while the expected was "0".
Server "A"
Server "B"
Testing done so far:
Run on Server "A"
Edit:
Just double checked and the entire R drive is Shared on Server "B".
I switch the path in the ssis package to "\server\R..." that this did not work either. Is there a difference between sharing a folder vs sharing a drive?
Switched the Agent Job to instead point to the dtsx on the File system.
Reran the Agent Job expecting it to fail, it did. This time it provided a workable error: "FileNotFoundError: [Errno 2] No such file or directory: 'R:\...\OUTPUT.txt"
Turns out I had updated the Agent Job and the "Execute Process Task Editor" with the UNC path but failed to do so in the python script; the script referenced the OUTPUT.txt file.
Updated the python script with the UNC path and the Agent Job completed successfully.