Replacing datasource does not work through arcpy

5519
22
05-01-2019 11:50 AM
JoeHershman
MVP Regular Contributor

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?

Thanks,
-Joe
0 Kudos
22 Replies
JeffMoulds
Esri Contributor

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
MVP Regular Contributor

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

Thanks,
-Joe
0 Kudos
JeffMoulds
Esri Contributor

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 

0 Kudos