ArcPy Layer updateConnectionProperties not working as expected

11826
19
Jump to solution
05-16-2019 03:09 PM
DianaBenedict1
New Contributor III

I am attempting to use Arcpy Layer updateConnectionProperties method to change the database connection for each layer in an ArcPro project from my SDEDEV environment to an SDEUAT environment. Note that both database are identical copies so they only vary in the database name/servername, instance name and PWD. In our organization this is a common task when we are publishing services in a DEV environment. Once we are ready for Users to Test (UAT) our apps and underlying services/maps we publish to UAT. This workflow allows us the ability to continue developing new stuff without affecting the users that are testing the stuff that was developed in previous deployments. When we used ArcMap we had a python toolbox tool that would re-map the database connections for each layer in a map from DEV to UAT and Finally to PROD once we were ready for the final deployments. Our goal is to have a similar workflow, I would like to do use some ESRI ArcPy Pro Python code to update the connection information. I assumed that the "updateConnectionProperties" method should be able to do this.

After researching and comparing other solutions and using ESRI documentation here I was able to come up with the following code:

import arcpy
aprx = arcpy.mp.ArcGISProject(r'C:\ProProjects\MyProj\TheProPoject.aprx')
for theMap in aprx.listMaps():
 print("%-24s %s" % ("Map Name:", theMap.name))
 for lyr in theMap.listLayers():
 if lyr.supports("CONNECTIONPROPERTIES"):
 print("%-24s %s" % ("layer Name:", lyr.name))
 lyr.updateConnectionProperties(lyr.connectionProperties,r'C:\ProProjects\MyProj\DatabaseConnections\ARCGISSERVER@SDEUAT2.sde', True, False)
aprx.save()
del aprx
print("done")

This solutions kinda works but with some caveats:

  1. The connection is not getting reset when using the SDE database connection that I currently have indicated. I know this connection works because I can use the same connection to manually reset a layer's datasource in ArcPro.
  2. I force the connection to get set WITHOUT validation (the last False parameter). This results in "broken" layer connections. Though this is not the prefered method, at this point I can open the ProProject and then select the "red-exclamation" broken layer for one and point the layer to the new desired database connection. The good news is that this fixes all other broken connections provided that it found the "same" data layers in the new database connection.
  3. This one is an interesting find: the only layers in my ArcPro Map Project that get reset to "broken" are the ones that inside a Group Layer. Any layers in a Map that are NOT in a Group Layer are ignored. The print inside the for each layer loop shows that I see the layer and this it indeed has a connectionproperty that I checking (lyr.supports("CONNECTIONPROPERTIES")) for before calling the updateConnectionProperties. For all the layers that are not in a group, I still need to manually set the layer datasource in the ArcPro project because it does not break the connection or even change it. (this does not seem to me like expected behavior)

Questions:

  1. Has anyone successfully used the updateConnectionProperties method to change from one database connection to another?
  2. Am I using this class correctly? Layer.updateConnectionProperties ...
  3. I see that ArcPy also has an updateConnectionProperties  for the entire APRX project. According to the ESRI documenation (above) I see the following "You have control of updating data sources for individual layers or tables, or you can update all the layers or tables in a common workspace at once" And all the sample look similar to what I have.

Thought, comments, suggestions are greatly appreciated.

Thanks,

Diana

1 Solution

Accepted Solutions
AlbertoAloe
Occasional Contributor

Hi guys,

the script below for me works fine. I'm updating the dictionary keys coming out of connectionProperties and running updateConnectionProperties method.

I'm changing database user and password doing for all sql server sde layers in a chosen map. If you need to change something else just update the relevant dictionary value

# Input parameters are  the chosen map within the current project, new user name and password
mapName = arcpy.GetParameterAsText(0)
newUser = arcpy.GetParameterAsText(1)
newUserPass = arcpy.GetParameter(2)
#===================================
#The default project is the current one....
aprx = arcpy.mp.ArcGISProject('CURRENT')
currentMap = aprx.listMaps(mapName)[0]

#Loop layers excluding group layers and basemap
for lyr in currentMap.listLayers():
    if not(lyr.isGroupLayer or lyr.isBasemapLayer): #I have to exclude group layers and basemaps of course.....
        try:
            if lyr.dataSource.split(',')[2].split('=')[1] == 'SQL Server': # basically if the layer has a sql server data source
                new_conn_prop = lyr.connectionProperties
                new_conn_prop['connection_info']['user'] = newUser
                new_conn_prop['connection_info']['password'] = newUserPass
                # Now i can update the connection properties dictionary of the layer
                lyr.updateConnectionProperties(lyr.connectionProperties,new_conn_prop)
                arcpy.AddMessage(lyr.name + ' has been updated !')

        except NameError:
            arcpy.AddMessage(lyr.name + ' does not have  a sql server data source')

Alberto

View solution in original post

19 Replies
BrettLessner
Occasional Contributor

Hi Diana,

I am having similar issues with updating layers to a SDE connection, and share the same three questions.

For me, I'm trying to bring in a workflow from ArcMap where I used the Layer.replaceDataSource(workspace_path, workspace_type, dataset_name) method.

In ArcGIS Pro, I temporarily set the layer source to a SDE feature class I wanted - through Layer Properties. I ran print(lyr.connectionProperties) to get a glimpse of what everything should be and copied it. Then reset the source to the .gdb fc, and attempted to run lyr.updateConnectionProperties(current connection info, new connection info). Still no luck...

-Brett

0 Kudos
DianaBenedict1
New Contributor III

Brett

If did you try to put the layer in your map in a Group Layer? I found that I was at least able to "break" the connection with the validate = false property. At least when you do this, you can reset the connection for all the "broken" layer connections at once by simply hitting the red exclamation. However, as I stated above I was unable to get anything to work if the layers were not in a group. 


Personally, I believe this is a bug. I am going to submit this bug via tech support and see if I can get some help there. Glad to know I am not the only one out there experiencing the same issue. 

Thanks for responding, maybe we will get some interest from others as well.

I will add more comments as I get more info.

Regards

Diana

0 Kudos
BrettLessner
Occasional Contributor

Diana,

No, I cut my losses for now, and went back to ArcMap for what I needed.

I think you're right about it being a bug - looks like others have similar issues:

Replacing datasource does not work through arcpy 

Updating connection info via python from SDE to File geodb - with a feature dataset 

Updating Data Source in ArcGIS PRO project with arcpy 

-Brett

0 Kudos
DianaBenedict1
New Contributor III

Just wondering if anyone else has figured this issue out or if ESRI staff is aware of the potential bug with the Python API updateConnectionProperties method. 

Updating Database Connections in ArcPro is becoming exceedingly time consuming and I was hoping to get the python script to updateConnectionProperties to work correctly. I am assuming that ESRI would prefer for us to continue to move forward and use ArcPro instead of taking a step backwards and revert to ArcMap simply because "updateConnectionProperties" is not working correctly.

Thanks

Diana

DianaBenedict1
New Contributor III

All,

I just spoke with ESRI Support and they indicated that this is a defect. Here is the bug # in case anyone else runs into this issue.

The Defect ID is: BUG-000112574

From now, when using the updateConnectionProperties method, I will set th Validate option to "False". This will at least break the connection in my ArcPro project and then I can set it manually by selecting on one of the broken red exclamation connections.

Cheers and good luck!

0 Kudos
MichaelVolz
Esteemed Contributor

Diana:

Are you saying that all the Pro-based python updateConnectionProperties method will do is break existing SDE connections and then you would need to manually go into each aprx file and hit the red exclamation point to reconnect to SDE once again?

0 Kudos
DianaBenedict1
New Contributor III

Michael,

No by default, the Pro-based python updateConnectionProperties method will not break the existing datasource connection. However, you can force it break the connection if you set the "Validate" property to False - as I have shown in the script in my original post. If Validate is set to False then it will break the connection and give you a red exclamation. However, I noted above in item #3 (in my original post) that this only occured in layers that were placed inside a group layer. I have given ESRI Tech Support all this information and I believe that they will be putting all this into the Defect ID listed above.  

I hope this helps. 

Regards,

Diana

0 Kudos
AlbertoAloe
Occasional Contributor

Hi guys,

the script below for me works fine. I'm updating the dictionary keys coming out of connectionProperties and running updateConnectionProperties method.

I'm changing database user and password doing for all sql server sde layers in a chosen map. If you need to change something else just update the relevant dictionary value

# Input parameters are  the chosen map within the current project, new user name and password
mapName = arcpy.GetParameterAsText(0)
newUser = arcpy.GetParameterAsText(1)
newUserPass = arcpy.GetParameter(2)
#===================================
#The default project is the current one....
aprx = arcpy.mp.ArcGISProject('CURRENT')
currentMap = aprx.listMaps(mapName)[0]

#Loop layers excluding group layers and basemap
for lyr in currentMap.listLayers():
    if not(lyr.isGroupLayer or lyr.isBasemapLayer): #I have to exclude group layers and basemaps of course.....
        try:
            if lyr.dataSource.split(',')[2].split('=')[1] == 'SQL Server': # basically if the layer has a sql server data source
                new_conn_prop = lyr.connectionProperties
                new_conn_prop['connection_info']['user'] = newUser
                new_conn_prop['connection_info']['password'] = newUserPass
                # Now i can update the connection properties dictionary of the layer
                lyr.updateConnectionProperties(lyr.connectionProperties,new_conn_prop)
                arcpy.AddMessage(lyr.name + ' has been updated !')

        except NameError:
            arcpy.AddMessage(lyr.name + ' does not have  a sql server data source')

Alberto

JeffMoulds
Esri Contributor

Starting at ArcGIS version 2.6, we improved this workflow so that you can use Enterprise Geodatabase Connection Files in the first parameter of the UpdateConnectionProperties function. You can now do something like the following code sample. 

import arcpy 
aprx = arcpy.mp.ArcGISProject(r'C:\Projects\YosemiteNP\Yosemite.aprx')
aprx.updateConnectionProperties(r'C:\Projects\YosemiteNP\DBConnections\TestGDB.sde',
                                r'C:\Projects\YosemiteNP\DBConnections\ProductionGDB.sde')

More info and code samples can be found here: Updating and fixing data sources—ArcGIS Pro | Documentation