topic Re: ArcPy Layer updateConnectionProperties not working as expected in Python Questions

ArcPy Layer updateConnectionProperties not working as expected

DianaBenedict1 — Sun, 12 Dec 2021 16:33:59 GMT

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:

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

Has anyone successfully used the updateConnectionProperties method to change from one database connection to another?
Am I using this class correctly? Layer.updateConnectionProperties ...
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

Re: ArcPy Layer updateConnectionProperties not working as expected

BrettLessner_Nsight — Fri, 24 May 2019 16:08:39 GMT

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

Re: ArcPy Layer updateConnectionProperties not working as expected

DianaBenedict1 — Fri, 24 May 2019 16:49:27 GMT

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

Re: ArcPy Layer updateConnectionProperties not working as expected

BrettLessner_Nsight — Fri, 24 May 2019 17:22:53 GMT

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

Re: ArcPy Layer updateConnectionProperties not working as expected

DianaBenedict1 — Thu, 18 Jul 2019 18:38:04 GMT

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

Re: ArcPy Layer updateConnectionProperties not working as expected

DianaBenedict1 — Fri, 26 Jul 2019 16:17:45 GMT

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!

Re: ArcPy Layer updateConnectionProperties not working as expected

MichaelVolz — Fri, 26 Jul 2019 16:51:14 GMT

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?

Re: ArcPy Layer updateConnectionProperties not working as expected

DianaBenedict1 — Fri, 26 Jul 2019 22:59:51 GMT

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

Re: ArcPy Layer updateConnectionProperties not working as expected

AlbertoAloe — Sat, 11 Dec 2021 22:40:44 GMT

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

Re: ArcPy Layer updateConnectionProperties not working as expected

BrianCunningham2 — Tue, 01 Oct 2019 20:36:17 GMT

I too was running into the same issue where the data source was not getting changed when using a .sde file.

I changed tactics and did as Alberto Aloe suggested and it worked like a charm. I even noticed that the script was running a little slower as it churned through each layer in each map, so I could tell that it was doing something.

Re: ArcPy Layer updateConnectionProperties not working as expected

JeffMoulds — Sat, 11 Dec 2021 22:40:47 GMT

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

Re: ArcPy Layer updateConnectionProperties not working as expected

DianaBenedict1 — Mon, 03 Aug 2020 16:09:41 GMT

Thank you for reaching out to this Thread to let your users know of the enhancement to the arcpy.mp updateConnectionProperties. This functionality will surely simplify our code.

Regards

Diana

Re: ArcPy Layer updateConnectionProperties not working as expected

JorgeMorteo — Sat, 11 Dec 2021 22:40:50 GMT

I used the

aprx.updateConnectionProperties in ArcGIS PRO 2.6 runs very slow as it goes through all 
the layers that need to be change.  It takes about four minutes each layer and at some point
it does not give me error and just seats there spinning.  In ArcMap was a 100 times faster.

import arcpy
import sys
CASE = sys.argv[1]
aprx = arcpy.mp.ArcGISProject("CURRENT")
aprx.updateConnectionProperties(r"C:\GIS_WorkSpace\Geodatabase\CASES\Shapes", r"X:\PD_Notify\Labels-2020\\" + CASE)
del aprx

Re: ArcPy Layer updateConnectionProperties not working as expected

JeffMoulds — Mon, 28 Sep 2020 17:21:29 GMT

Jorge, thanks for the feedback. Yes, we know in some cases referencing a project via CURRENT and executing updateConnectionProperties is slow. You can speed up the time by setting the validate parameter in updateConnectionProperties to False. It is also faster if you run the script out of the app. That being said, I havent seen speeds as slow as 4 mins per layer. What type of data are the layers?

Re: ArcPy Layer updateConnectionProperties not working as expected

JorgeMorteo — Mon, 28 Sep 2020 17:27:47 GMT

They are shapefiles. I wonder if that is the problem. Just to test, I am going export some of the shapefiles to Geodatabase and see if that is the problem.

Re: ArcPy Layer updateConnectionProperties not working as expected

ChrisHolmes — Thu, 11 Mar 2021 18:27:09 GMT

Hi Jeff,

Does updateConnectionProperties require that feature class name in old and new be the same?

I have the situation where we have moved a number of feature classes from file geodatabase to sde, but the feature classes have also been renamed in sde to conform to corporate standards. So, for example:

before: Data.gdb\roads; after: database.sde\transportation

before: Data.gdb\rivers; after: database.sde\hydrology

Is it possible to fix these broken links using updateConnectionProperties. Seemed pretty easy to do this in arcMap, just starting to see how/if it will be possible to accomplish the same in Pro.

thanks

Re: ArcPy Layer updateConnectionProperties not working as expected

JackFanZhang — Tue, 26 Oct 2021 11:21:10 GMT

Though share my experience here. I had the same issue where using updateConnectionProperties(l.connectionProperties, newConnectionProperties) on updating Layer data source from one FileGDB to another FileGDB didn't work. the process returned successful message without any warning. Couldn't work out why for a while. After adding validate=false and run again, the layer became broken, and that's because the new FileGDB doesn't have the same feature class that the layer referred to. After sorting the feature class issue, the function works as expected.

The help doc says

Validate: If set to True, the connection properties will only be updated if the new_connection_info value is a valid connection. If it is not valid, the connection will not be replaced.

I would thought it would be necessary to improve the updateConnectionProperties function that when validate is True, and if the new connection is invalid, it should return a warning to make the user's attention.

Re: ArcPy Layer updateConnectionProperties not working as expected

KimOllivier — Sun, 04 Sep 2022 10:45:35 GMT

Here I am in 2022 with ArcGIS Pro 3.01 installed still looking to see why it does not work with a couple of simple file geodatabases!

Re: ArcPy Layer updateConnectionProperties not working as expected

RutgerDujardin — Fri, 10 Mar 2023 15:37:25 GMT

Hi,

I'm trying as well to use the updateConnectionProperties function. Just in its most basic form, linking a new file geodatabase to an existing .aprx. Old and new databases have exactly the same structure. In my case, the function changes correctly the path's within the .aprx file. But when I open the .aprx it gives my only broken layer connections. When I click the red-exclamation mark and direct to exactly the same location/feature class it retrieves the data.

My question: Why does the red-exclamation mark appears if the path is set correctly? Why can't the .aprx file represent the data when opening it?

The code I'm using:

aprx = arcpy.mp.ArcGISProject(path_Template_aprxFile)

aprx.updateConnectionProperties(None, path_newDatabase)

aprx.saveACopy(path_New_aprxFile)

If anyone can help solving my problem, be welcome.

Re: ArcPy Layer updateConnectionProperties not working as expected

RutgerDujardin — Mon, 24 Apr 2023 14:30:46 GMT

Applied strategy to solve my problem. Updating the connection for each layer separately:

aprx = arcpy.mp.ArcGISProject(path_Template_aprxFile)

for m in aprx.listMaps():

for layer in m.listLayers():
newCP = layer.connectionProperties
newCP['connection_info']['database'] = path_newDatabase
layer.updateConnectionProperties(layer.connectionProperties, newCP)
aprx.saveACopy(path_New_aprxFile)