Change connection properties from EGDB to Feature Service

[JoeBorgione]

I have a number of ArcGIS Pro projects where I need to change one layer that used to be a feature class in our EGDB (aka SDE) to a feature service.

The online help provides various approaches and in this post, @GaetanPRU provides some code to update the connection properties of a shape file.  Following that lead:

# I have a list created a priori of my Pro projects:
for a in aprxList:
    aprx = arcpy.mp.ArcGISProject(a[0])
    map = aprx.listMaps("Live SDE")[0]
    layer = map.listLayers("MSD.SLCOMSD.AddressGridMSD")[0]
    cp = layer.connectionProperties

If I list cp, I get the dictionary of the connection properties:

cp
{'dataset': 'MSD.SLCOMSD.AddressGridMSD',
 'workspace_factory': 'SDE',
 'connection_info': {'authentication_mode': 'DBMS',
  'database': 'myDB',
  'dbclient': 'sqlserver',
  'db_connection_properties': '"bla bla bla',
  'password': '<yeahRight>',
  'instance': '"sde:sqlserver:bla bla bla',
  'server': 'bla bla bla',
  'user': 'myUser',
  'version': 'sde.DEFAULT'}}

However, I'm unsure as to what my next step is to update the connection properties to a feature service.

[Anonymous User]

Id look at a layers connection properties that is a feature service and compare what it is to what you have and update by the dictionary method.

[JoeBorgione]

That's a great suggestion!  I'll post up what I find!

[JoeBorgione]

After a couple hours of trial and error, I'm tossing in the towel and getting a cold adult beverage.

The online help describes using an existing dictionary and a replacement dictionary to update the properties.  I just can make sense of it.

aprx = arcpy.mp.ArcGISProject('N:\\GIS\\AddressDataManagement\\TestDir\\CUP2020_000038\\cup2020_000038.aprx')
m = aprx.listMaps()[0]
l = m.listLayers("MSD.SLCOMSD.AddressGridMSD")[0]

existingDict = {'connection_info': {'authentication_mode': 'DBMS',
                                    'db_connection_properties':'xxxdb',
                                    'instance':'sde:sqlserver:sxxxdb',
                                    'server': 'xxxdb',
                                    'dataset': 'MSD.SLCOMSD.AddressGridMSD',
                                    'workspace factory':'SDE'}}
    
replaceDict = {'connection_info': {'url':'https://xxx.org/server/rest/services/Hosted/AddressCoordinateGrid/FeatureServer'},
                                   'dataset': '0',
                                   'alias':'AddressCoordinateGrid',
                                   'workspace_factory':'FeatureService'}
l.updateConnectionProperties(existingDict,replaceDict)
aprx.saveACopy('N:\\GIS\\AddressDataManagement\\TestDir\\CUP2020_000038\\cup2020_000038NEW.aprx')

 When I run this as is, the properties of my feature layer looks like this:

Notice the value in the url row.  In the python code above, line 5 has 'authentication_mode': 'DBMS'  : if I leave that part out, it won't make any updates at all; the feature layer in the TOC still has the red exclamation mark in front of it. But when I leave it in, I get the weird properties value shown above.  In closing for today, if I list the connection properties through arcpy, it shows the sde connection properties but the feature url :

pprint.pprint(l.connectionProperties)
{'connection_info': {'authentication_mode': 'DBMS',
                     'database': 'xxx',
                     'db_connection_properties': '"xxxdb',
                     'dbclient': 'sqlserver',
                     'instance': '"sde:sqlserver:xxxdb',
                     'password': '<xxx>',
                     'server': 'xxxdb',
                     'url': 'https://xxx.org/server/rest/services/Hosted/AddressCoordinateGrid/FeatureServer',
                     'user': 'SLCOmsd',
                     'version': 'sde.DEFAULT'},
 'dataset': '0',
 'workspace_factory': 'FeatureService'}

It appears to be pulling from the correct source, but I don't really want those other properties populated and I would really like the Properties pane to be accurate as well....

[Anonymous User]

You'd think that it would remove the dictionary keys that are not in the update dict, but it looks like its just adding to it so I would take a copy of the old dict and '' the keys that would no longer apply as part of the new dictionary.  updateConnectionProperties seems like it is one of those methods that could use some deeper documentation/ more diverse examples.

[arunachalammak]

Hi Joe,

Here is the simple solution, after a couple of research I found properties are updating correctly for me. You missed to add other properties from existingDict to replaceDict and set its value to an empty string, like in the sample below. Try it and see the magic.

Thanks

Arun

replaceDict = lyr.connectionProperties
replaceDict["dataset"] = str(dsID)
replaceDict["workspace_factory"] = "FeatureService"
replaceDict["connection_info"]["url"] = str(baseURL)
replaceDict["connection_info"]["authentication_mode"] = ""
replaceDict["connection_info"]["dbclient"] = ""
replaceDict["connection_info"]["db_connection_properties"] = ""
replaceDict["connection_info"]["password"] = ""
replaceDict["connection_info"]["instance"] = ""
replaceDict["connection_info"]["server"] = ""
replaceDict["connection_info"]["user"] = ""
replaceDict["connection_info"]["version"] = ""
lyr.updateConnectionProperties(lyr.connectionProperties, replaceDict)

[GaetanPRU]

Hello,

If you want have the same properties you can use the CIM properties of an other layer to update it. It's a python object with all specifications about data connection.

aprx = arcpy.mp.ArcGISProject('N:\\GIS\\AddressDataManagement\\TestDir\\CUP2020_000038\\cup2020_000038.aprx')
m = aprx.listMaps()[0]
l = m.listLayers("MSD.SLCOMSD.AddressGridMSD")[0]
new_l = m.listLayers("xxxxxxxxxxxxxxxx")[0]

l_CIM = l_CIM.getDefinition("V2")
new_l_CIM = new_l.getDefinition("V2")

l_CIM.featureTable.dataConnection = new_l_CIM.featureTable.dataConnection

l.setDefinition(l_CIM)

aprx.saveACopy('N:\\GIS\\AddressDataManagement\\TestDir\\CUP2020_000038\\cup2020_000038NEW.aprx')

Online help

But you must have the referenced layer somewhere in your project. If you have many differents layers it may take a while.

EDIT:

I have the same issue than you if I use updateConnectionProperties() method.

I tried by copying directly connection properties from layer to the other and I have 'authentication_mode': 'DBMS'  too

layer.updateConnectionProperties(layer.connectionProperties, new_layer.connectionProperties)

I think it's a bug or we can't update workspace_factory SDE to FeatureService.

[JoeBorgione]

Thank you @GaetanPRU -  the problem I see with using another layer for the CIM properties is I'd have to add the reference layer to the set of project files and that essentially defeats the purpose.  Now as I type this, I wonder if a better way would be to simply delete the existing Egdb layer and replace it with the Feature Service Layer...

... I think it's a bug or we can't update workspace_factory SDE to FeatureService....

Pretty much what I've been thinking too.  I mean, ESRI is pushing hosted feature services and with good reason However there needs to be an efficient set of tools that work and allow us to migrate from the old days of direct connection data bases to the modern approach of hosted feature services. 

This from a guy with one foot out the door because he:

[JoeBorgione]

Removing and replacing seems to do the trick.  We have a cloud based ArcGIS Server to which we publish data, and then we point back to it for an item in our AGOL.  Here is the code that I got to work for me:

import arcpy
aprx = arcpy.mp.ArcGISProject('N:\\GIS\\AddressDataManagement\\TestDir\\CUP2020_000038\\cup2020_000038.aprx')
m = aprx.listMaps()[0]
rmlyr = m.listLayers("MSD.SLCOMSD.AddressGridMSD")[0]

m.removeLayer(rmlyr)

#addlyr = r'https://name/server/rest/services/Hosted/AddressCoordinateGrid/FeatureServer/0'

addlyr = r'https://ourAgol/home/item.html?id=################'

m.addDataFromPath(addlyr)
aprx.saveACopy('N:\\GIS\\AddressDataManagement\\TestDir\\CUP2020_000038\\cup2020_000038NEW.aprx')

Lines 10 and 12 are the same data in our case: this just illustrates that the addDataFromPath can take an ArcGIS Enterprise rest end or and AGOL item.

[Anonymous User]

Thanks for posting this journey! We are moving some of our data to hosted feature services and need a way to change the source in our existing maps. I've been going through these same exact problems (sometimes its nice not to feel alone)!

I also noticed that the updateConnectionProperties didn't remove the enterprise geodatabase specific parameters from the connection info portion of the connection properties dictionary. It would be nice to know if this is a bug or expected and we need to figure out another way to achieve this workflow. I believe it will become more common as folks move to using more hosted feature services. 

Thanks for suggesting the remove and add layer as a workaround!

original connection properties:

{'dataset': 'EGDB.OWNER.WorkOrders', 'workspace_factory': 'SDE', 'connection_info': {'authentication_mode': 'OSA', 'database': 'EGDB', 'dbclient': 'sqlserver', 'db_connection_properties': 'db_server', 'instance': 'sde:sqlserver:db_server', 'server': 'db_server', 'version': 'sde.DEFAULT'}}

connection properties after using updateConnectionProperties to replace the enterprise geodatabase source to a service url:

{'dataset': '1', 'workspace_factory': 'FeatureService', 'connection_info': {'authentication_mode': 'OSA', 'database': 'EGDB', 'dbclient': 'sqlserver', 'db_connection_properties': 'db_server', 'instance': 'sde:sqlserver:db_server', 'server': 'db_server', 'url': 'https://<serviceURL>/FeatureServer', 'version': 'sde.DEFAULT'}}