topic Re: Update SDE Connection Properties in ArcGIS Pro Project Via ArcPy in ArcGIS Pro Questions

Update SDE Connection Properties in ArcGIS Pro Project Via ArcPy

Clint_Glover — Tue, 04 Jun 2019 19:20:44 GMT

Currently within my organization, password rules have been put in place that requires us to update our Oracle passwords every 90 days. Due to these rules, I am in the process of creating a python script for both ArcMap mxds and ArcGIS Pro projects. We currently have our SDE connection files in a shared directory to make this process easier. When a password change is required, we update the Oracle password and then update the password for the associated SDE connection file in the shared directory. Our intentions are to reuse the SDE connection files after a password changes, so our python scripts essentially should just reset each layer's connection info back to itself, which would then contain the updated password for the associated SDE file.

The ArcMap python script has been created and works correctly using the .replaceDataSource function. When I began working on the ArcGIS Pro python script, I ran into issues using the updateConnectionProperties function for layers within a map in a ArcGIS Pro project that used SDE connection files in our shared directory. I attempted to set the new SDE connection file for each layer back to itself (which works with the replaceDataSource function for ArcMap), due to the SDE connection file staying the same, with only the password being updated. I have also attempted to reset each layer's connection properties to a completely new SDE file, but that doesn't appear to work either. None of ESRI's examples show using updateConnectionProperties to go from one SDE connection to another SDE connection file. Does anyone know if this is possible or if there is an alternate route I can take to complete this task? For reference, I am using the latest version of ArcGIS Pro (2.3.3) and our SDE connection files are pointed to an Oracle database (12c).

Re: Update SDE Connection Properties in ArcGIS Pro Project Via ArcPy

Suleyman-Arslan — Sun, 12 Dec 2021 02:01:03 GMT

I am also trying to swich layer data sources from file geodatabase to enterprise geodatabase.

I followed guidelines provided in Updating and fixing data sources. Unfortunately none of the below samples worked for me.

import arcpy
aprx = arcpy.mp.ArcGISProject(r'C:\......\Test1.aprx')

m = aprx.listMaps("Scene1")[0]
m.updateConnectionProperties(r'C:\....\xxx.gdb', r'C:\Temp\yyy.sde', False, False)
aprx.save()
del aprx‍‍‍‍‍‍‍

import arcpy
aprx = arcpy.mp.ArcGISProject(r'C:\......\Test.aprx')


find_dict = {'connection_info': {'database': 'C:\\.....\\xxx.gdb'},  
             'workspace_factory': 'File Geodatabase'}
replace_dict1 = {'connection_info': {'db_connection_properties': '10.4.xx.xx/yyyy',
                                    'instance': 'sde:oracle:10.4.xx.xx/yyyy',
                                    'server': '10.4.xx.xx'}}
replace_dict = {'connection_info': {'authentication_mode': 'DBMS',                                                             
                     'dbclient': 'oracle',                     
                     'instance': '10.4.xx.xx/yyyy',                     
                     'password': '****',                     
                     'server': '10.4.xx.xx',                     
                     'user': 'user',                     
                     'version': 'sde.DEFAULT'}, 'workspace_factory': 'SDE'}

m = aprx.listMaps("Scene1")[0]
for lyr in m.listLayers():
    if lyr.isFeatureLayer & lyr.isBroken
        lyr.updateConnectionProperties(find_dict, replace_dict)

aprx.save()
del aprx‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

import arcpy
aprx = arcpy.mp.ArcGISProject(r'C:\.......\Test1.aprx')

m = aprx.listMaps("Scene1")[0]
lyr1 = m.listLayers('Layer1')[0]
for lyr in m.listLayers():
    if lyr.isFeatureLayer and lyr.isBroken and lyr.name=="Layer2":
        find_dict = {'connection_info': {'database': r'C:\.....\xxx.gdb'}, 
                 'dataset': 'dataset1', 
                 'workspace_factory': 'File Geodatabase'}
        replace_dict = {'connection_info': {'authentication_mode': 'DBMS',
                                            'database': 'C:\\Temp\\connection.sde',
                                            'db_connection_properties': '10.4.xx.xx',
                                            'dbclient': 'oracle',
                                            'instance': '10.4.xx.xx/instance',
                                            'password': '*****',
                                            'server': '10.4.xx.xx',
                                            'user': 'user',
                                            'version': 'sde.DEFAULT'}, 'dataset': 'schema.dataset1', 'workspace_factory': 'SDE'}
        lyr.updateConnectionProperties(find_dict, replace_dict,True,False)


aprx.save()
del aprx
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Update SDE Connection Properties in ArcGIS Pro Project Via ArcPy

Clint_Glover — Tue, 11 May 2021 18:26:13 GMT

I actually called ESRI tech support on this particular issue and below is the Python code ESRI gave me as the solution to this issue. Instead of resetting the connection properties to another sde connection file, this Python simply just updates the password for each layer. After running some quick initial tests, it seems to work for me, with the exception of a layer that contains a join.

Python code ESRI provided

import arcpy, pprint p = arcpy.mp.ArcGISProject('current') m = p.listMaps()[0] for l in m.listLayers(): conProp = l.connectionProperties conProp['connection_info']['password'] = '<new password>' l.updateConnectionProperties(l.connectionProperties, conProp) p.save() ## This may work as wlel if they are sure that only their database layers are broken in the project. ##for l in m.listLayers(): ## if l.isBroken == 'True': ## conProp = l.connectionProperties ## conProp['connection_info']['password'] = 'sa' ## l.updateConnectionProperties(l.connectionProperties, conProp) ##p.save()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

When there is a layer with a join, conProp can not find the 'connection_info', and this is due to it being split into two different sections, one for the layer's connection info and one for the connection info for the join. From what I found, it looks like you access the connection info for the layer and the join with the following code:

conProp['source']['connection_info']['password'] conProp['destination']['connection_info']['password']‍‍

When I put it all together, it should look like this:

import arcpy, pprint p = arcpy.mp.ArcGISProject('current') m = p.listMaps()[0] for l in m.listLayers(): conProp = l.connectionProperties conProp['source']['connection_info']['password'] = '<new password>' conProp['destination']['connection_info']['password'] = '<new password>' l.updateConnectionProperties(l.connectionProperties, conProp) p.save() ## This may work as wlel if they are sure that only their database layers are broken in the project. ##for l in m.listLayers(): ## if l.isBroken == 'True': ## conProp = l.connectionProperties ## conProp['connection_info']['password'] = 'sa' ## l.updateConnectionProperties(l.connectionProperties, conProp) ##p.save()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

After making that change, the Python still did not work to fix those layers and/or save the Pro project in Pro 2.3.3. It appears to run through the first layer of the map in the Pro project and then it stops with no errors or anything.

EDIT: All of the issues with the Python listed above not updating layers with joins and saving the Pro projects were fixed after I updated from Pro 2.3.3 to 2.4.1. Something in the Pro update fixed the issues.

Re: Update SDE Connection Properties in ArcGIS Pro Project Via ArcPy

Clint_Glover — Thu, 11 Jul 2019 11:17:50 GMT

I have not attempted to switch a layer's data source from a file geodatabase to an enterprise geodatabase so unfortunately I'm of no help to you in this particular situation. If I get some spare time, I'll attempt it and will let you know what I find out.

Re: Update SDE Connection Properties in ArcGIS Pro Project Via ArcPy

Suleyman-Arslan — Fri, 12 Jul 2019 13:58:33 GMT

After spanding a lot of time with arcpy, I developed my first add-in with ArcGIS Pro SDK for changing data source. I changed data sources for hundreds of layers with add-in.

Re: Update SDE Connection Properties in ArcGIS Pro Project Via ArcPy

Arne_Gelfert — Tue, 17 Sep 2019 20:14:43 GMT

Hey Clint, thanks for posting. I had virtually the same issue and started this thread a while back. In the end, I settled for your approach as well. That's so ESRI though! Instead of letting you replace one reference to an SDE file with a reference to another SDE file, they tell you to hardcode a password into the script.

I guess you could just prompt for a password during execution. Somewhere I also have a snipped for reading in a password without showing it. Could be that it only works with arcgis module though. - Thanks again.

Re: Update SDE Connection Properties in ArcGIS Pro Project Via ArcPy

Clint_Glover — Tue, 17 Dec 2019 17:56:24 GMT

The join issue I noted above has been resolved when updating to ArcGIS Pro 2.4. I'm not sure exactly what the difference was but something in the update fixed it when running the script within Pro 2.4.

Re: Update SDE Connection Properties in ArcGIS Pro Project Via ArcPy

Clint_Glover — Tue, 17 Dec 2019 18:11:16 GMT

Yes I totally agree. Nothing ever seems straightforward with ESRI. I'm not sure how we are going to handle it yet (not really using ArcGIS Pro just yet for publishing our web services), but we will come up with something in the near future.

Re: Update SDE Connection Properties in ArcGIS Pro Project Via ArcPy

ELIZABETH50 — Wed, 15 Apr 2020 17:41:39 GMT

Did you share the ArcGIS add-in?

Re: Update SDE Connection Properties in ArcGIS Pro Project Via ArcPy

diaconori — Tue, 09 Feb 2021 12:02:01 GMT

Why is this the accepted answer when the author of this post clearly states that it still does not work?

Re: Update SDE Connection Properties in ArcGIS Pro Project Via ArcPy

diaconori — Wed, 17 Feb 2021 14:17:23 GMT

What's the accepted answer to this? Clint's answer is marked as the accepted answer yet he clearly states that it does not solve the problem.

EDIT:

I tried to change the SDE connection in ArcMap using Python 2.7 and the following function:

lyr.findAndReplaceWorkspacePath(old_path, new_path, True)

It works. You guys may want to consider using the ArcMap route even though it seems counter intuitive...

Re: Update SDE Connection Properties in ArcGIS Pro Project Via ArcPy

Clint_Glover — Tue, 11 May 2021 18:27:12 GMT

Sorry I am just now noticing your response from Feb 2021. So it appears I simply forgot to add more context to the solution response. All issues with the Python not updating layers with joins and saving the Pro projects were fixed after I updated from Pro 2.3.3 to 2.4.1. Something in the Pro update fixed the issues. I will edit my solution response to reflect this as well. I am currently running Pro 2.7.1 and have not tested this out in that version to ensure it still works with no issues.

I will say our organization is just now needing to utilize this Python for our Pro projects in our dev environment. We will know for sure soon if this Python works like we need it to. If anything needs to be changed I will let you know.

Re: Update SDE Connection Properties in ArcGIS Pro Project Via ArcPy

diaconori — Thu, 10 Jun 2021 08:26:02 GMT

The accepted answer does not provide a solution that works regardless of ArcGIS Pro's version number. We're running the latest version of ArcGIS Pro and the accepted answer does not work.

I've left a new reply on this thread with a working solution, however it does not include using ArcGIS Pro. I recommend that you accomplish the above through the ArcMap route. ConnectionProperties are simply just over-engineered and way too complex to work with.