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:
Questions:
Thought, comments, suggestions are greatly appreciated.
Thanks,
Diana
Solved! Go to Solution.
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
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
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
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
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
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!
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?
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
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
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