Updating Data Source in ArcGIS PRO project with arcpy

[Arne_Gelfert]

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?

[TobiSellekaerts]

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!

[Anonymous User]

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

[AlbertoAloe]

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

[Arne_Gelfert]

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

[JeffMoulds]

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 

[BinhLe]

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.

[Anonymous User]

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. 

[ClaudiuVarcalin]

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.

[mody_buchbinder]

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

[diaconori]

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.