Update SDE Connection Properties in ArcGIS Pro Project Via ArcPy

7978
12
Jump to solution
06-04-2019 12:20 PM
Clint_Glover
New Contributor III

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

1 Solution

Accepted Solutions
Clint_Glover
New Contributor III

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.

View solution in original post

12 Replies
Suleyman-Arslan
Occasional Contributor

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
0 Kudos
Clint_Glover
New Contributor III

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.

0 Kudos
Clint_Glover
New Contributor III

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.

Clint_Glover
New Contributor III

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. 

0 Kudos
diaconori
New Contributor III

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

0 Kudos
Clint_Glover
New Contributor III

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.

0 Kudos
diaconori
New Contributor III

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. 

0 Kudos
Suleyman-Arslan
Occasional Contributor

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.

ELIZABETHF_T_
New Contributor III

Did you share the ArcGIS add-in? 

0 Kudos