sql-serverssisetlsql-agent-jobnetwork-drive

SQL Server Agent Job doesn't work for shared drive


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?


Solution

  • 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.