topic Re: Updating Data Source in ArcGIS PRO project with arcpy in ArcGIS API for Python Questions

Updating Data Source in ArcGIS PRO project with arcpy

Arne_Gelfert — Sun, 12 Dec 2021 09:19:50 GMT

Trying to update data source for layers in ArcGIS Pro maps programmatically. So, I must be misunderstanding the syntax for doing that explained here. Because the following does not work:

import arcpy

aprx = arcpy.mp.ArcGISProject(r'path to my project file')

#This gets me list of all the maps
maplist = aprx.listMaps()

#If I only want those belonging to MyGroup
mygroup = aprx.listMaps('MyGroup')[0]

#To get the layers in that group
grouplayers = mygroup.listLayers()

new_sdeConn = r'path to my SDE connection file'

for lyr in grouplayers:
    old = lyr.connectionProperties	#also tried lyr.connectionProperties['connection_info']
    lyr.updateConnectionProperties(old, new_sdeConn)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This doesn't throw an error. it just doesn't do update the data sources as desired. The ESRI example goes like:

aprx.updateConnectionProperties(r'C:\Projects\YosemiteNP\Vector_Data\Yosemite.gdb',
                                r'C:\Projects\YosemiteNP\DBConnections\Server.sde')‍‍‍‍‍‍

Does this maybe only work for the project as a whole?

Re: Updating Data Source in ArcGIS PRO project with arcpy

StephenM — Sun, 12 Dec 2021 09:19:52 GMT

The issue you're having is that lyr.connectionProperties returns a dictionary but your new connection is a workspace path. You just need to access the right key in the dictionary:

for lyr in grouplayers:
    old = lyr.connectionProperties['connection_info']['database']
    lyr.updateConnectionProperties(old, new_sdeConn)

Re: Updating Data Source in ArcGIS PRO project with arcpy

Arne_Gelfert — Sun, 12 Dec 2021 09:19:55 GMT

Stephen, thanks. Appreciate you chiming. I realize that connection_info is a dictionary. But I wasn't sure how to use it. Keyword "database' only updates the database. I'm actually trying to switch this to use a different account (new 'user' and 'password') and was hoping to do so using a new SDE connection file. Can you maybe not to do?

If you look at the other keys in the dict, I need more than 'database' to be updated.

{'authentication_mode': 'DBMS',
 'database': 'Prod',
 'dbclient': 'sqlserver',
 'db_connection_properties': 'myserver',
 'password': '<*********>',
 'instance': 'sde:sqlserver:myserver',
 'server': 'myserver',
 'user': 'myaccount',
 'version': 'dbo.DEFAULT'}

I am not an expert on these SDE connection files. But my understanding has always been that they store a reference to DB server/instance, DB name, authentication (and/pr password). So basically what's in the dictionary above. So merely updating 'database' in the dict wouldn't be sufficient, I think. To be safe, I tried it, and to no effect.

Re: Updating Data Source in ArcGIS PRO project with arcpy

StephenM — Sun, 12 Dec 2021 09:19:57 GMT

Ah, right. I forgot that you're using SDE connections. To switch users I'd try modifying ['connection_info]['user'] and ['connection_info']['password'].

for lyr in layers:
    old_conn = lyr.connectionProperties
    new_conn = old_conn
    new_conn['connection_info']['user'] = new_user
    new_conn['connection_info']['password'] = new_pw
    lyr.updateConnectionProperties(old_conn, new_conn)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Updating Data Source in ArcGIS PRO project with arcpy

Arne_Gelfert — Sun, 12 Dec 2021 09:20:00 GMT

I've tried passing in a complete new dict or just the 'user' and 'password' values as you suggested. I've tried the approach of referencing another layer's connectionProperties suggested here:

lyr = m.listLayers('some other layer')[0]
l.updateConnectionProperties(l.connectionProperties, lyr.connectionProperties)‍‍

None of it is working. Darn!

Re: Updating Data Source in ArcGIS PRO project with arcpy

StephenM — Tue, 26 Mar 2019 01:11:10 GMT

Hm, maybe the credentials can't be modified in the way the other values can. I do notice that there aren't examples in the documentation that show changing users in the connection properties. I had some success changing a connection from an OS authentication connection to a DBMS connection, but I don't have multiple named DB users to test changing credentials.

Re: Updating Data Source in ArcGIS PRO project with arcpy

BrettLessner_Nsight — Fri, 24 May 2019 15:08:50 GMT

Has anyone solved this? I'm trying to move a workflow from Map to Pro, and this is hanging me up. It was so much easier with the lyr.replaceDataSource().

Re: Updating Data Source in ArcGIS PRO project with arcpy

TobiSellekaerts — Tue, 02 Jul 2019 12:45:31 GMT

Sadly I have given up banging my head against the desk trying to make this work. I have searched through three other threads on GeoNet of people having a variation of this same problem without resolution:

updateConnectionProperties

ArcPy Layer updateConnectionProperties not working as expected

Replacing datasource does not work through arcpy

and elsewhere:

Repoint enterprise geodatabase connection using ArcPy in ArcGIS Pro - Geographic Information Systems Stack Exchange

In the end I found an old ArcMap .mxd with the same data also pointing to the wrong database, used good ole reliable lyr.replaceDataSource() to fix the data sources, imported the mxd as a new map in Pro, then copied the data over into my original map. Sad but true.

Re: Updating Data Source in ArcGIS PRO project with arcpy

Arne_Gelfert — Wed, 03 Jul 2019 22:00:29 GMT

As with so many other instances, not finding any info on how to do it, I abandoned this a while back. But I'm sure I will have to look at it again some day and will remember this post.

Re: Updating Data Source in ArcGIS PRO project with arcpy

MichaelVolz — Thu, 18 Jul 2019 19:28:16 GMT

Tobi:

I have not tried to recreate my update SDE datasource scripts from ArcMap to Pro yet so I have not experienced this hardship yet, but I have found that the Set Data Source(s)... functionality of ArcCatalog has not been ported over to Pro. Although this ArcCatalog functionality does not work on batch mxds, I have found it very useful for moving publishing mxds from development to test to production where the SDE database is different for each environment.

How many Pro aprxs are you looking to update the SDE connection properties for?

Set Data Sources Tool in Pro

Re: Updating Data Source in ArcGIS PRO project with arcpy

TobiSellekaerts — Thu, 01 Aug 2019 09:27:13 GMT

Just one. But within this one project there are 35 layers to be repathed. I will exhaust many attempts at automation before changing them all manually!

Re: Updating Data Source in ArcGIS PRO project with arcpy

AlbertoAloe — Sun, 12 Dec 2021 09:20:03 GMT

Hi everybody.

I run into this post and I realized that I may have something simple but useful.

The script below just goes layer by layer in a chosen map within the 'CURRENT' project and update user and password on sqlserver sde layers.

It is meant to be used within a script tool (pass the password as 'hidden string' data type). The logic excludes group and basemap layers and checks the data source in the connectionProperties looking for data source hints in the dictionary (I just look for SQL Server). The try/except block catches NameError exception for other layers

import arcpy
# 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')
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I hope this helps

Alberto

Re: Updating Data Source in ArcGIS PRO project with arcpy

Arne_Gelfert — Sun, 12 Dec 2021 09:20:06 GMT

Folks, I was reminded of this thread when stumbling across a similar one - here. Turns out that while I would've preferred to be able to update references from one SDE connection file (with embedded credentials) to another and do so programmatically, simply running an update on [user] and [password] was the quickest.

conProp = layer.connectionProperties

#depending on what all you're updating, you may have additional
#keys you need to address
#Examples: https://pro.arcgis.com/en/pro-app/arcpy/mapping/updatingandfixingdatasources.htm

conProp['connection_info']['user'] = newuser
conProp['connection_info']['password'] = newpassword

layer.updateConnectionProperties(layer.connectionProperties, conProp)‍‍‍‍‍‍‍‍‍‍

Re: Updating Data Source in ArcGIS PRO project with arcpy

Anonymous User — Mon, 09 Dec 2019 18:44:56 GMT

I've contemplated going the ArcMap route as well. Hopefully, I can avoid that, but...

Re: Updating Data Source in ArcGIS PRO project with arcpy

JeffMoulds — Sun, 12 Dec 2021 09:20:08 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')‍‍‍‍

You can also call updateConnectionProperties on the project, map, layer, table or LayerFile.

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

Re: Updating Data Source in ArcGIS PRO project with arcpy

BinhLe — Tue, 25 Aug 2020 22:43:42 GMT

Jeff,

I used this script and nothing happened. I went into Catalog View to try and change the sde connection path and it won't save. I can find and replace it, but when I hit apply, it would revert back to the old one.

Re: Updating Data Source in ArcGIS PRO project with arcpy

Anonymous User — Wed, 26 Aug 2020 14:20:45 GMT

There are 2 possibilities for why this isn't working:

1) I have not had luck with swapping sde connection files, and I think it is because in some situations, the data source may not recognize the input sde connection file path you have entered even if you entered a legitimate path. How data sources "think" about what their connection to sde really is is VERY COMPLICATED (usually, the path is on the c drive of your computer, for example...) and that's probably why ArcGIS Pro is moving to the "Connection Properties" dictionary to handle this.

2) The "updateConnectionProperties" method has a couple arguments that are not shown here. One is "validate" (true/false) and setting it to "false" got my code to work. Apparently, it wasn't seeing the output sde connection as valid, even though it was. It may be excessively finnicky. I think the argument right after the "to" sde connection file is whether or not to update joins, then this one, so:

updateConnectionProperties(Old Source, New Source, True <go ahead and update joins>, False<Don't require the output sde connection to be "valid">) might work.

Re: Updating Data Source in ArcGIS PRO project with arcpy

ClaudiuVarcalin — Thu, 12 Nov 2020 08:13:00 GMT

Afer a lot of trial and error, and help from this thread, I found this to work for me to change both the dataset (feature class) name and the workspace connection:

First, replace the dataset name with the new one and use False for the schema testing parameter. In most cases this will break the layer data connection, but this is only temporary.

lyr.updateConnectionProperties(lyr.connectionProperties,{'dataset':<datasetNameInDestinationWorkspace>},False,False)

The second step is to repoint to the new workspace. This time use the default value (True) for the schema testing parameter. The application will read the feature class properties, apply them to the layer, and consequently fixe the broken data source connection.

lyr.updateConnectionProperties(<currentWorkspacePath>,<newWorkspacePath>)

The workspace paths can point to file geodatabases or .sde connection files.

Re: Updating Data Source in ArcGIS PRO project with arcpy

mody_buchbinder — Thu, 21 Jan 2021 13:11:21 GMT

So I run into the same problem. I needed to change layer that point to Oracle to point to sql server with a different name.

The main issue here is to turn a full path of feature class into a layer so you can get connection properties or just move symbology from one layer to anther.

Below os my code, two different way to turn FC into layer in pro arcpy.

It is ugly, I think we need a simple mathod just like replaceDataSource in ArcMap to do it.

My code below

lyrFile = r"C:\temp\.ppp1.lyrx" newFc = r"D:\temp\local express.sde\data2.DBO.PPP1_EXP_0" connDic = {} def main(): #connDic = fc2layer1().connectionProperties connDic = fc2layer2().connectionProperties print("FC props=" + str(connDic)) print("dataset= %s" % (connDic["dataset"])) print("connection info= %s" % (connDic["connection_info"])) # get layer to update origLyrF = arcpy.mp.LayerFile(lyrFile) origLyr = origLyrF.listLayers()[0] oldConnDic = origLyr.connectionProperties # update connection and dataset name oldConnDic["dataset"] = connDic["dataset"] oldConnDic["connection_info"] = connDic["connection_info"] origLyr.updateConnectionProperties(origLyr.connectionProperties,oldConnDic) # add to map aprx = arcpy.mp.ArcGISProject("CURRENT") aprxMap = aprx.listMaps("rel")[0] aprxMap.addLayer(origLyr) def fc2layer1(): # create a layer by adding to map aprx = arcpy.mp.ArcGISProject("CURRENT") aprxMap = aprx.listMaps("rel")[0] aprxMap.addDataFromPath(newFc) # must remove this layer later # search for layer in toc (added with alias name) desc = arcpy.Describe(newFc) lyr = aprxMap.listLayers(desc.aliasName)[0] return lyr def fc2layer2(): # create a lyr from lyrx file arcpy.MakeFeatureLayer_management(newFc,"lyr1") arcpy.SaveToLayerFile_management("lyr",r"C:\temp\xx.lyrx") lf = arcpy.mp.LayerFile(r"C:\temp\xx.lyrx") lyr = lf.listLayers()[0] return lyr if __name__ == '__main__': main()

Re: Updating Data Source in ArcGIS PRO project with arcpy

diaconori — Thu, 10 Jun 2021 08:27:32 GMT

I can also confirm that this does not work in 2021.

I've implemented various variations of the solutions proposed on the posts related to this issue, and to conlcude things, none of them works.

EDIT:

Only way to do this is to go the ArcMap route. ConnectionProperties is just over-engineered and too complex to work with. Use lyr.findAndReplaceDataSource and you'll accomplish this in no time.