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?

[JoeHershman]

Just for yucks I ran the same code on a Feature Class at the root level instead of in a Feature Dataset.  In this scenario the the update does work.  Still odd because in the properties window the instance setting is inconsistent 

In the one that was changed:

When set by adding in standard methods it looks like this where the instance shows the database name, but through code it has the path.  I did try to change set the instance to use the database name in the target dictionary but that gave a bad datasource

[SrihariDarapaneni]

There are lot of confusions over names dataset from arcpy's 'connection_info' to desktop ArcGIS Pro layer properties. 

arcpy's 'connection_info' dataset means featureclass name but in desktop ArcGIS Pro is different. 

only instance, and db_connection_properties in connection_info has to be set properly, as you know, in database and in Arc Pro property there is know value for dataset    , if you keep instace, db_connection_properties properly and dataset as featureclass name  it will resolve the issue. 

and if change feature class name in layer properties then it fine dont worry about other values, still esri has to modify their layer property dialog box. 

It is not important to this method where featureclass is whether it is in dataset(Arc Catalog/ArcMap terminolgy) or at root level. 

I am successfully changing databases with is method without watching layer property dialog box. 

[JoeHershman]

If you have this working please post the exact code that works.  I have changed to set the dataset to the unqualified name of the dataset as you suggest and the result is the same, only the layer at the top level will change anything in feature dataset does not update correctly.

Your post that shows the json is unclear, it shows setting the database item to FeatureClassName which makes no sense, although I did try just in case.  This is not Oracle so there is no instance in the way Oracle uses instance, it is a different database in the same Sql Server 'Instance'

# source
{'connection_info': {'authentication_mode': 'DBMS',
                     'database': 'Staging',
                     'db_connection_properties': 'portal07',
                     'dbclient': 'sqlserver',
                     'instance': 'sde:sqlserver:portal07',
                     'password': '<*********>',
                     'server': 'portal07',
                     'user': 'gis',
                     'version': 'sde.DEFAULT'},
 'dataset': 'Staging.GIS.PipelineAssembly',
 'workspace_factory': 'SDE'}

#target
{'connection_info': {'authentication_mode': 'DBMS',
                     'database': 'fort_collins',
                     'db_connection_properties': 'portal07',
                     'dbclient': 'sqlserver',
                     'instance': 'sde:sqlserver:portal07',
                     'password': 'gis',
                     'server': 'portal07',
                     'user': 'gis',
                     'version': 'sde.DEFAULT'},
 'dataset': 'PipelineAssembly',
 'workspace_factory': 'SDE'}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I have run the code replacing everything and also just running with 'database' and 'dataset' being changed.  Both have the same result, classes within a Feature Dataset become invalid and it shows the Feature Dataset incorrect.  I ran the same code a second time changing the database back and when this is done the datasource becomes valid again (because now the Feature Dataset is correct).  I see no way to change the Feature Dataset to an unqualified name so do not know what I am missing

Again, if you have a working sample fo changing the database please post.  I am thinking perhaps there is a difference with Oracle and Sql Server but I could just be missing something.  

Thanks

[curtvprice]

Have you asked Esri support yet? They can probably explain what workflow you need.

[JoeHershman]

Was hoping to not have to go down that path if possible.  But likely the only route, although if it is a bug (or unsupported feature) as I suspect, it is doubtful I would have any resolution within a timeframe that would be useful.  Not to mention that amount of time that will need to be spent with tech support repeating everything in this thread with multiple people

[curtvprice]

If you start an incident, be sure and point the tech support people to this thread. In the past that has helped grease the process and save on the back and forth. Also once you have an incident number, they have a record of it and any past and future workarounds or fixes in the database. Please do open an incident, this way others can benefit from your suffering so it isn't wasted!

I have to say I've always been happy with their help, even if it doesn't mean an immediate fix, at least I know of any workarounds right away.

[SrihariDarapaneni]

This sample code i am running sucessfully on oracle db

import arcpy, os, pprint
import traceback
from os import path

project_folder = r'D:\Project\'
project_file = r'sample.aprx'

project_path = os.path.join(project_folder, project_file)
aprx = arcpy.mp.ArcGISProject(project_path)

Source_fc = 'SM.sm__installs'
target_fc = 'SM.sm__installs'

targetInstance = 'sde:oracle$sde:oracle11g:ABCD_P'
targetUser = 'sde'
targetPwd = 'sde123'

for map in aprx.listMaps():
   for layer in map.listLayers():
      if layer.connectionProperties == None:
         continue
   
   sourceConn = layer.connectionProperties
   targetConn = sourceConn
   print(sourceConn)
   targetConn['dataset'] = target_fc  #which is not required if pointing to same fc in other database
   targetConn['connection_info']['instance'] = targetInstance
   targetConn['connection_info']['user'] = targetUser
   targetConn['connection_info']['password'] =targetPwd
   #targetConn['connection_info']['db_connection_properties'] ='xDatabase/xInstance'  # this is required for SQL db
   print(targetConn)
   layer.updateConnectionProperties(sourceConn, targetConn)

aprx.save()
del aprx

[JoeHershman]

Thank you for the sample, but this is different from what I am doing.  There is a difference in sql server in that it has a database name.  This is different from changing an instance, which is what is being done above.  Because of how the data connection seems to be maintaining the database name, it is not making the change correctly.  If I where only changing the server or instance but keeping the database name the same this issue does not arise.  I can change from dbServer01 to dbServer02 as long as the name of the Sql Server database is the same

In Sql Server the identifier is not 'Database/Instance', it is 'Server/Instance' or usually just 'Server' because in most cases the default instance is used which does not have an identifier

Thanks

-Joe

[Anonymous User]

It seems there is a bug in arcpy.mp.updateConnectionProperties where if you are trying to update feature class is in a feature dataset in an Enterprise Geodatabase (.sde) to another EGDB. According to this thread Esri knows about it and will fix in future release (unconfirmed) https://community.esri.com/thread/228083-updating-connection-info-via-python-from-sde-to-file-geodb-... 

[JoeHershman]

Thanks.