layer.updateConnectionProperties Does Not Update Relates

[JeffPickles2]

In ArcPy 3.7 (ArcPro 2.9 environment), I am looking to promote an Aprx from one environment to another, changing datasets accordingly. I am using layer.updateConnectionProperties to switch from QA SDE layers to Prod SDE layers. This works as expected for the layers, themselves, but I cannot get the related table (also SDE based) to also update to Prod data sources.

auto_update_joins_and_relates doesn't work, nor does spelling out the new environmental parameters by updating the connection properties dictionary.

currentDb = QA_SDE
outputDb = Prod_SDE

currentEnv = QA_RegisteredDir
outputEnv = Prod_RegisteredDir

inputAprx = QA_Aprx
targetAprx = Prod_Aprx

aprx = arcpy.mp.ArcGISProject(inputAprx)

maps = aprx.listMaps()

for map in maps:
    layers = map.listLayers()
    for layer in layers:
        if not layer.supports('dataSource'):
            continue

        elif currentDb in layer.dataSource:
            newProps = layer.connectionProperties
            oldProps = layer.connectionProperties

            newProps['connection_info']['db_connection_properties'] = outputDb
            newProps['connection_info']['instance'] = 'sde:sqlserver:' + outputDb
            newProps['connection_info']['server'] = outputDb
            newProps['connection_info']['authentication_mode'] = 'OSA'

            if 'relates' in layer.connectionProperties:
                if isinstance(newProps['relates'], list):
                    newProps['relates'][0]['connection']['connection_info']['db_connection_properties'] = outputDb
                    newProps['relates'][0]['connection']['connection_info']['instance'] = 'sde:sqlserver:' + outputDb
                    newProps['relates'][0]['connection']['connection_info']['server'] = outputDb
                    newProps['relates'][0]['connection']['connection_info']['authentication_mode'] = 'OSA'

            layer.updateConnectionProperties(current_connection_info = oldProps, new_connection_info = newProps, auto_update_joins_and_relates = True)
        
        elif currentEnv in layer.dataSource:
            layer.updateConnectionProperties(current_connection_info = currentEnv, new_connection_info = outputEnv)

aprx.saveACopy(targetAprx)

When run, newProps dictionary updates accordingly to reflect the Prod sourced related table. However, when updateConnectionProperties is run, the layer source is in Prod but the relate is still QA sourced. This behavior occurs both when auto_update_joins_and_relates is set to True and to False.

What am I missing here?

[SzymAdamowski · Accepted solution]

Probably you're not missing anything. Function updateConnectionProperties is not very reliable (putting it politely). Last resort is using CIM. I was able to replicate your problem using FileGeodatabase (so it is probably not related to connection type). You can rely entirely on CIM or allow updateConnectionProperties to update feature class data source and then use CIM to update relate data source.

Following piece of code will update relate connection for FileGDB:

path=r'E:\GIS_DATA\GDB1.gdb'
lyrCIM = layer.getDefinition("V3")
dc = lyrCIM.featureTable.relates[0].dataConnection #works only for the first relate
dc.workspaceConnectionString = fr"DATABASE={path}" #there must be no spaces 
layer.setDefinition(lyrCIM)

What you can do is to save one of your layers as .lyrx file and then investigate JSON structure (by changing extension to .json) - as described in Python CIM Access 

When you hack necessary elements of workspaceConnectionString (which are probably different in your case), you're code could be like this:

currentDb = QA_SDE
outputDb = Prod_SDE

currentEnv = QA_RegisteredDir
outputEnv = Prod_RegisteredDir

inputAprx = QA_Aprx
targetAprx = Prod_Aprx

aprx = arcpy.mp.ArcGISProject(inputAprx)

maps = aprx.listMaps()

for map in maps:
    layers = map.listLayers()
    for layer in layers:
        if not layer.supports('dataSource'):
            continue

        elif currentDb in layer.dataSource:
            newProps = layer.connectionProperties
            oldProps = layer.connectionProperties

            newProps['connection_info']['db_connection_properties'] = outputDb
            newProps['connection_info']['instance'] = 'sde:sqlserver:' + outputDb
            newProps['connection_info']['server'] = outputDb
            newProps['connection_info']['authentication_mode'] = 'OSA'
            layer.updateConnectionProperties(current_connection_info = oldProps, new_connection_info = newProps, auto_update_joins_and_relates = True)
            if 'relates' in layer.connectionProperties:
                lyrCIM = layer.getDefinition("V2")
                dc = lyrCIM.featureTable.relates[0].dataConnection
                dc.workspaceConnectionString = "?"##Adjust it accordingly
                layer.setDefinition(lyrCIM)

       
        elif currentEnv in layer.dataSource:
            layer.updateConnectionProperties(current_connection_info = currentEnv, new_connection_info = outputEnv)

aprx.saveACopy(targetAprx)

 Good luck.

[JeffPickles2]

That is indeed what I was missing. Thank you! 

For anyone else running into a similar issue in the future, I took Szym's suggestion and combined it with a ModifyRelateProperties script that is within the ESRI supplied CIM_Examples_Pro25_v1 found here: https://www.arcgis.com/home/item.html?id=8772f61319584882bb697ba003030636. 

Here's my working code: 

currentDb = QA_SDE
outputDb = Prod_SDE

currentEnv = QA_RegisteredDir
outputEnv = Prod_RegisteredDir

inputAprx = QA_Aprx
targetAprx = Prod_Aprx

aprx = arcpy.mp.ArcGISProject(inputAprx)

maps = aprx.listMaps()

for map in maps:
    layers = map.listLayers()
    for layer in layers:
        if not layer.supports('dataSource'):
            continue

        elif currentDb in layer.dataSource:
            newProps = layer.connectionProperties
            oldProps = layer.connectionProperties

            newProps['connection_info']['db_connection_properties'] = outputDb
            newProps['connection_info']['instance'] = 'sde:sqlserver:' + outputDb
            newProps['connection_info']['server'] = outputDb
            newProps['connection_info']['authentication_mode'] = 'OSA'

            layer.updateConnectionProperties(current_connection_info = oldProps, new_connection_info = newProps)

            if 'relates' in layer.connectionProperties:
                lyrCIM = layer.getDefinition('V2')
                dc = lyrCIM.featureTable.relates[0].dataConnection
                dc.workspaceConnectionString = dc.workspaceConnectionString.replace(currentDb, outputDb)
                layer.setDefinition(lyrCIM)

        
        elif currentEnv in layer.dataSource:
            layer.updateConnectionProperties(current_connection_info = currentEnv, new_connection_info = outputEnv)

aprx.saveACopy(targetAprx)