Updating connection info via python from SDE to File geodb - with a feature dataset

[FraserHand1]

Hi,

I'm clipping data and moving to a file geodb from SDE and fixing up datasources  for a symbolised Pro project before publishing.

This works for the main part unless the feature class is in a feature dataset. Doing the below works for 90% of my feature classes - if the FC is in a FD the source breaks as Pro can't find the FD in the filegeodb. (note the validate false was for troubleshooting)

con_props = {'connection_info':{'database': temp_filegeodb,
                                'authentication_mode': '',
                                'dbclient': '',
                                'db_connection_properties': '',
                                'password': '',
                                'instance': '',
                                'server': '',
                                'user': '',
                                'version': ''},
             'dataset': name,
             'workspace_factory': 'File Geodatabase',
             'feature_dataset': ''
             }
print(con_props)
a_layer.updateConnectionProperties(a_layer.connectionProperties, con_props,validate=False)

yet I can't see a flag to clear the feature dataset from the layer.

So we end up with a broken datasource as the layer is still looking for the original feature dataset. Is there an easy way to clear this - I haven't seen anything in the doco.

Before:

After:

surely this property is exposed in the connection_info dictionary? Right?

Ta

Fraser

[BruceHarold]

Hi Fraser, someone will take a look (and BTW 'Ta' doesn't travel outside NZ)

[Anonymous User]

Hi Bruce and Fraser, I am also experiencing the same issue with updateConnectionProperties method on the Layer class (assuming same issue is present for the same method on different classes like Map, Project, etc...). Feature Dataset is not exposed in connectionProperties python dictionary property. Thanks!

[FraserHand1]

Hi,

Esri are aware of it and it will be addressed in a future release. You'll need to work around in the meantime.

Thanks

Fraser

[MarkoPolo]

When!? It has been months and we (a very large million dollar ESRI client) are waiting. This is blocking a critical organisation wide enterprise project. I want to scream at ESRI right now for not fixing this. Please PM. 

There is no workaround. 

[Anonymous User]

Hi Marko Polo,
If you have not already I would strongly suggest lodging a case with technical support. They'll be able to actively look into it.
Thanks,
Chris

[JohnCarlee]

Are there any updates on this issue? (11/15/2019)

[JeffMoulds · Accepted solution]

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 Enterprise Geodatabase to File Geodatabase. It will also handle the differences in feature dataset names between the two data formats (line 38).

# new File Geodatabase
newFGDB = r"C:\backup\UC2019\arcpy.mp\DataSources\SDE2FGDB.gdb"

# Enterprise Geodatabase
entServer = "ss2014-104"

# folder to search
folder = r"C:\backup\UC2019\arcpy.mp\DataSources" 

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={entServer}") != -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 - 4 steps:
                        # ===========================================
                        # 1.) Change the connection string to point to the new file gdb
                        dc.workspaceConnectionString = f'DATABASE={newFGDB}'
                        
                        # 2.) If the data is in a Feature Dataset, then update it 
                        #     That is, remove the user name [all formats] and database [SQL Server] 
                        #     e.g. change mydb.myuser.MyFDSName to MyFDSName
                        if hasattr(dc, "featureDataset"):
                            dc.featureDataset = dc.featureDataset[dc.featureDataset.rfind('.')+1:]
                            
                        # 3.) Change the workspace type from SDE (Enterprise) to FileGDB
                        dc.workspaceFactory = 'FileGDB'
                        
                        # 4.) Change the dataset name 
                        #     That is, remove the user name [all formats] and database [SQL Server]. 
                        #     e.g. change mydb.myuser.MyFCName to MyFCName
                        dc.dataset = dc.dataset[dc.dataset.rfind('.')+1:]
                        
                        # Update layer CIM (commit the changes)
                        lyr.setDefinition(lyrCIM)
                    else:
                        print(f"    LAYER : {lyr.name}")
                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('+++++++++') ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[LachlanWainwright]

Greate script Jeff, you just saved me so so much pain.  Slotted it in and bang, rolled it.

thanks mate!

[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. This will work if the feature dataset name in the Enterprise Geodatabase is the same as the File Geodatabase (not including the user and database prefix - meaning "mydb.myuser.MyFDSName" is equivalent to "MyFDSName")

import arcpy 
aprx = arcpy.mp.ArcGISProject(r'C:\Projects\YosemiteNP\Yosemite.aprx')
aprx.updateConnectionProperties(r'C:\Projects\YosemiteNP\DBConnections\Server.sde',
                                r'C:\Projects\YosemiteNP\Local_Data\YosemiteLocal.gdb') ‍‍‍‍‍‍‍‍‍‍‍‍‍

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