Replacing datasource does not work through arcpy

[JoeHershman]

Writing a script to replace the datasource in a project.  I have tried doing this by replacing the .sde file path on the apex object.  Tried by replacing at the map level and also at the layer level.  none of these methods work.  It runs without error but open the project back up and everything still pints to the original source.

Following samples located here:

Updating and fixing data sources—ArcPy | ArcGIS Desktop 

Has anyone ever gotten this to actually work?

[JeffMoulds]

The UpdateConnectionProperties doesn't handle different Feature Dataset names (when changing the SQL Server database, the Feature Dataset name changes, e.g. from "myDB.myUSER.myFeatureDataSet" to "myNewDB.myUSER.myFeatureDataSet". We hope to fix this in a future release. Starting in version 2.4, there is a work around using the arcpy cim module. Python CIM access is available which will provide finer-grained access to more settings/capabilities. We dont have data source examples in the help yet, but here is the overview help topic and a video.

Help topic: https://pro.arcgis.com/en/pro-app/arcpy/mapping/python-cim-access.htm

Video: https://www.youtube.com/watch?v=8wgt8bKD0Ww&feature=youtu.be

The following script will update layer data sources from one SQL Server Enterprise Geodatabase to another. It will also handle the differences in feature dataset names between the two databases (see line 49).

# Existing Enterprise Geodatabase server and database
oldServer = "ss2014-104"
oldDatabase = "sde1041en"

# New Enterprise Geodatabase server and database
newServer = "ss2016-105"
newDatabase = "gdb1051en"

# folder to search
folder = r"C:\Temp\Projects" 

import arcpy, os

for file in os.listdir(folder):
    if os.path.splitext(file)[1].lower() == '.aprx':
        print("")
        print(f"APRX      : {file}")
        aprx = arcpy.mp.ArcGISProject(os.path.join(folder, file))
        for m in aprx.listMaps():
            print(f"  MAP     : {m.name}")
            for lyr in m.listLayers():
                if lyr.supports("dataSource"):
                    if lyr.dataSource.find(f"Server={oldServer}") != -1:
                        # FOUND A LAYER TO UPDATE!
                        print(f"    LAYER : {lyr.name} -> UPDATING LAYER'S DATASOURCE!")
                        
                        # Access layer CIM
                        lyrCIM = lyr.getDefinition("V2")
                        dc = lyrCIM.featureTable.dataConnection
                        
                        # Update the connection properties - 3 steps
                        # ==========================================
                        
                        # 1.) Update the connection string (server, instance, database, version, etc)
                        cs = dc.workspaceConnectionString
                        cs = cs.replace("SERVER=" + oldServer, "SERVER=" + newServer)
                        cs = cs.replace("INSTANCE=sde:sqlserver:" + oldServer, "INSTANCE=sde:sqlserver:" + newServer)
                        cs = cs.replace("DB_CONNECTION_PROPERTIES=" + oldServer, "DB_CONNECTION_PROPERTIES=" + newServer)
                        cs = cs.replace("DATABASE=" + oldDatabase, "DATABASE=" + newDatabase)
                        cs = cs.replace("VERSION=sde.DEFAULT", "VERSION=dbo.DEFAULT")
                        dc.workspaceConnectionString = cs
                        
                        # 2.) Update the dataset name
                        #     E.g. change "myDB.myUSER.myFeatureClass" to "myNewDB.myUSER.myFeatureClass"
                        dc.dataset = dc.dataset.replace(oldDatabase, newDatabase)
                        
                        # 3.) If the data is in a Feature Dataset, then update it  
                        #     E.g. change "myDB.myUSER.myFeatureDataSet" to "myNewDB.myUSER.myFeatureDataSet"
                        if hasattr(dc, "featureDataset"):
                            dc.featureDataset = dc.featureDataset.replace(oldDatabase, newDatabase)
                        
                        # Update layer CIM
                        lyr.setDefinition(lyrCIM)
                        
                    else:
                        print(f"    LAYER : {lyr.name}")
                    
        # save a copy of the updated APRX
        aprx.saveACopy(os.path.join(folder, 'output', file))
        
print('')
print('+++++++++') 
print('+ DONE! +') 
print('+++++++++') ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[JoeHershman]

Hopefully this helps someone in the future.  We had to make the changes by hand 6+ months ago 

[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. Instead of using the above arcpy CIM workaround, 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 

[cjdwalshV1]

I'm currently experiencing the same issue here. We have a publishing script to publish mapservices to different environments with different databases. We are slowly migrating from Oracle to SQL and so I'm testing the script at the minute with our new SQL Dev environment.

The Mapservices I'm publishing have their data sources set using the dev sql sde file and I'm using the same sde file to test the publishing script. The expected output is that the the connection properties ultimately should look the same after the call to updateConnnectionProperties, however the Dataset names are different. I'm running ArcPro 3 on my PC.