I'm having a problem with publishing a Geoprocessing service based on a Python script tool where the tool works perfectly when run from ArcGIS Pro, but after I publish it to my ArcGIS Enterprise server as a GP service, it doesn't work, fundamentally because the publishing process is mangling the path to the .sde file that it uses to connect to the database where the business data live.
In my code, I have this:
class SwapProcessorBase(object):
"""Base class for the SWAP-DSS geoprocessing services"""
self.dbConnectionPath = None
def __init__(self):
#I've tried a number of variations on this line, including an absolute path, just the filename, et cetera, and they all have the same result.
self.dbConnectionPath = os.path.join(arcpy.env.packageWorkspace, r'db.sde')
#Compose a path to an actual table, to be queried later.
self.fc_pwsdetails = os.path.join(self.dbConnectionPath, r'swapdss.dbo.anth_tblSysMstr')
Later, we attempt to query the table that self.fc_pwsdetails refers to, like this:
whereClause = "PWS_ID='{0}'".format(self.pwsId)
self.pwsDetails = self.doTableQuery(self.fc_pwsdetails, whereClause)
The relevant doTableQuery function:
def doTableQuery(self, fc, whereClause:str):
ret = None
if(fc != None):
#So, this is what we should be able to do...
recordSet = arcpy.RecordSet()
recordSet.load(fc, whereClause)
ret = recordSet
return ret
When running in ArcGIS Pro, it works perfectly. When running on the server, after publishing as a GP Service, it fails, with the following error, at recordSet.load(fc, whereClause):
RuntimeError: RecordSetObject: Cannot open table for Load
If I go and look at the Python script that's actually running on the server, I notice that this line:
self.dbConnectionPath = os.path.join(arcpy.env.packageWorkspace, r'db.sde')
Has been changed to this:
self.dbConnectionPath = os.path.join(arcpy.env.packageWorkspace, g_ESRI_variable_1)
And at the beginning of the file, g_ESRI_variable_1 is defined thusly:
g_ESRI_variable_1 = os.path.join(arcpy.env.packageWorkspace,'..\\..\\..\\..\\..\\..\\..\\..\\..\\Documents\\ArcGIS\\Projects\\Swap3\\db.sde')
The core of the problem seems to be that, after it gets mangled by the publishing process, the string literal path to db.sde is now wrong, even thought it gets copied over to the server. If I edit the script on the server (as the Admininistrator) to once again include the correct path to db.sde, the GP service works properly. So, how do I get the publishing process to stop mangling the path to my .sde file? Is there way to either:
1. Turn that "feature" off completely, and publish the script file exactly as I wrote it, with responsibility for path correctness on me
2. Manually define the new values for anything it mangles, without hand hacking the published file on the server, which doesn't seem to be the intended workflow
3. Get it to at least mangle the path correctly, so that it works.
?
The thought has occurred to me that this is occurring because the Python scripts are being stored in something like c:\users\myusername\source\repos\projectname\subprojectname\, and the ArcGIS project is c:\users\myusername\documents\Arcgis\projects\swap3, but I have other projects I've handled this way, and those work perfectly.
The version of ArcGIS Pro in question is 2.9.1. The version of ArcGIS Enterprise in question 10.8.1.
