topic Re: Updating connection info via python from SDE to File geodb - with a feature dataset in Python Questions

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

FraserHand1 — Fri, 10 Dec 2021 23:28:39 GMT

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?

Fraser

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

BruceHarold — Thu, 31 Jan 2019 15:47:43 GMT

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

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

Anonymous User — Tue, 05 Mar 2019 14:29:36 GMT

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!

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

FraserHand1 — Tue, 05 Mar 2019 20:38:10 GMT

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

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

MarkoPolo — Thu, 20 Jun 2019 04:15:07 GMT

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.

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

Anonymous User — Fri, 21 Jun 2019 00:46:17 GMT

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

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

JohnCarlee — Fri, 15 Nov 2019 18:12:41 GMT

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

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

JeffMoulds — Fri, 10 Dec 2021 23:28:42 GMT

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('+++++++++') ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

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

LachlanWainwright — Wed, 03 Jun 2020 23:50:28 GMT

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

thanks mate!

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

JeffMoulds — Fri, 10 Dec 2021 23:28:45 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. 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

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

LindsayMillard1 — Fri, 07 Aug 2020 03:53:35 GMT

Hi Jeff,

My organisation has just upgraded to ArcGIS Pro 2.5.2, so won't be going to 2.6 for at least a little while.

Is there a way in 2.5.2 to adapt your 'workaround' script above to repoint a project's data sources from a file geodatabase to an enterprise (SQL) geodatabase?

Our databases are typically flat in structure, without feature datasets, joins or other complications, so we pretty much want to replace the .gdb path with a .sde path, although I know it's not that simple...

Given the different connection properties for file and enterprise geodatabases, I'm not sure how to translate this to your above solution.

Any assistance would be most appreciated!

Lindsay.

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

JeffMoulds — Fri, 10 Dec 2021 23:28:48 GMT

Re-sourcing data from File GDB to Enterprise GDB should be pretty straight forward in 2.5.2, especially if data set names are the same between the two geodatabases. For example:

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

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

LindsayMillard1 — Tue, 11 Aug 2020 01:18:58 GMT

Hi Jeff,

Thanks very much for your fast response.

I agree that re-sourcing should be straightforward... but it doesn't work for me as you have it scripted above.

I can run the equivalent of your script within an ArcGIS Pro session and, despite running for a period of time, the data sources remain unchanged.

Likewise, I can run the script in a standalone Python shell that saves a copy of the 'updated' APRX with the same non-results.

I assume this is related to BUG-000112574, so was hoping to use the 'Alternate Solution' proposed on that page of two dictionaries, but there is no clear direction on how to do this.

Are you able to confirm that your script runs in ArcGIS Pro 2.5.2?

If not, can you advise on the appropriate dictionary syntax to use to achieve the required outcome?

Thanks again, your assistance is much appreciated.

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

JeffMoulds — Tue, 11 Aug 2020 22:40:13 GMT

From the description of your workflow, the process should be pretty straight forward. The bug you mentioned shouldn't be a factor. And the little three line script in my previous reply should work. Can you email me a small project package that demonstrates the problem? jmoulds@esri.com.

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

MadhumithaArumugam — Mon, 24 Feb 2025 15:46:58 GMT

Hi Jeff, I have a similar situation where I'm trying to update the datasource of a QUERY FEATURE CLASS from SDE to FileGDB. Using your script I am able to update the connection, but the query doesn't get updated. How do I achieve this?

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

shay-geo — Wed, 11 Jun 2025 20:19:59 GMT

This does work for Features, Rasters, Features with Joins but haven't been able to get this to work for Standalone Tables. There must be some other ways to do it.