topic Re: Change connection properties from EGDB to Feature Service in Python Questions

Change connection properties from EGDB to Feature Service

JoeBorgione — Mon, 01 Nov 2021 19:13:53 GMT

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.

Re: Change connection properties from EGDB to Feature Service

Anonymous User — Mon, 01 Nov 2021 19:21:13 GMT

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.

Re: Change connection properties from EGDB to Feature Service

JoeBorgione — Mon, 01 Nov 2021 19:47:49 GMT

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

Re: Change connection properties from EGDB to Feature Service

JoeBorgione — Mon, 01 Nov 2021 22:47:38 GMT

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

Re: Change connection properties from EGDB to Feature Service

Anonymous User — Mon, 01 Nov 2021 23:48:54 GMT

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.

Re: Change connection properties from EGDB to Feature Service

GaetanPRU — Tue, 02 Nov 2021 10:10:01 GMT

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.

Re: Change connection properties from EGDB to Feature Service

JoeBorgione — Tue, 02 Nov 2021 16:23:04 GMT

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:

Re: Change connection properties from EGDB to Feature Service

JoeBorgione — Tue, 02 Nov 2021 16:22:13 GMT

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.

Re: Change connection properties from EGDB to Feature Service

Anonymous User — Tue, 30 Nov 2021 22:53:00 GMT

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'}}

Re: Change connection properties from EGDB to Feature Service

arunachalammak — Mon, 28 Feb 2022 10:27:45 GMT

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)

Re: Change connection properties from EGDB to Feature Service

arunachalammak — Mon, 28 Feb 2022 10:37:58 GMT

Hi All,

Thanks

Arun

Re: Change connection properties from EGDB to Feature Service

JoeBorgione — Mon, 28 Feb 2022 14:25:30 GMT

@arunachalammak - Thanks for your suggestion! I retired at the end of 2021 and haven’t written a line of Python since!

Re: Change connection properties from EGDB to Feature Service

DorothyShreve — Tue, 18 Jul 2023 19:35:04 GMT

I'm running into this same issue in ArcGIS Pro 3.1.2. Figured it was a bug, and glad I found this thread as it was very helpful. Posting code here to hopefully help someone else.

This code specifically re-sources a layerfile as we have different symbology in ArcGIS Pro and simply removing and replacing the layer with the symbology from the feature service is not what we want.

# Get AGOL Layer information new_cp = agol_lyr.connectionProperties new_cp['connection_info']['dataset'] = '0' new_cp['connection_info']['alias'] = 'Layer Alias' new_cp['connection_info']['workspace_factory'] = 'FeatureService' for lyr in temp_m.listLayers()[2:]: old_cp_info = lyr.connectionProperties['connection_info'] # if the key in the original connection_info is not in the new cp # add the key as a blank value to the new cp for key in old_cp_info: if key not in list(new_cp['connection_info'].keys()): new_cp['connection_info'][key] = '' lyr.updateConnectionProperties(lyr.connectionProperties, new_cp) print(lyr.connectionProperties)

Would be great if this was fixed sometime soon.