Publishing script tool as Geoprocessing Service - paths mangled, crash ensues

04-08-2022 12:49 PM
New Contributor II

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.

0 Kudos
2 Replies
MVP Frequent Contributor

You should just reference a local SDE file for publishing, then if it's registered in the data store it's all good to go.

arcgis desktop - Connecting Geoprocessing Service to ArcSDE? - Geographic Information Systems Stack ...

0 Kudos
New Contributor II

Thanks for following up!

Am I not referencing a "local SDE?"  I've got a file in my project "db.sde," which is what it uses when run locally to connect to the database.  That .sde file is registered with the server's data store - I have several map services based on maps in the same project that use the same file, and they work properly.

The problem I'm having is that, after publishing, it butchers the path to the copy of that .sde file that it makes.  The path should be something like ".\\db.sde," but it's inserting a bunch of "..\\"es into the path, and then a bunch more directories that exist on my desktop, but not the server.

Is there some other way I'm supposed to access the .sde file than what I showed above, where it would then get the path correct?

0 Kudos