SQL Server Agent Job doesn't work for shared drive

Question

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"

• Microsoft SQL Server 2019
• SSIS Package named "Test.dtsx" that executes the Python script (test.py) on the mapped drive "R"
• Mapped drive named ("R") that is pointed to the Script folder on Server "B"
• SQL Server Credential made using the AD Service Account
• SQL Server Proxy made and pointed to the AD Service Account Credential, Active for all subsystems
• SSIS Package (Test.dtsx) deployed to the Integration Services Catalogs
• SQL Agent Job references the Integration Services Catalogs Test.dtsx package
• • Owner: AD Service Account | Run as: Proxy

Server "B"

• Microsoft SQL Server 2019
• Script folder in the R drive with a Python script (test.py) that appends the word "Hello World" to a Text file (output.txt) in the same folder as the python script
• AD Service Account has permissions on the R drive (Full control | Read & execute)

Testing done so far:

Run on Server "A"

• SSIS package (Test.dtsx) runs successfully when executed manually in visual studio as the AD Service Account
• SSIS package (Test.dtsx) runs successfully when executed in Integration Services Catalogs as AD Service Account
• SSIS package (Test.dtsx) Fails when executed in SQL Server Agent Jobs | Owner: AD Service Account | Run as: Proxy

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?

Answer 1

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.