Replacing datasource does not work through arcpy

JoeHershman · ‎05-01-2019

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

JoeHershman · ‎05-01-2019

This is the basic code that is being used

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

project_folder = r'D:\...'
project_file = r'project.aprx'

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

db_source = 'source'
db_target = 'target'

for map in aprx.listMaps():
    for layer in map.listLayers():
        if layer.connectionProperties == None: 
            continue

        #replace db name in fully qualified dataset name
        source_dataset = layer.connectionProperties['dataset']
        target_dataset = str(source_dataset).replace(db_source, db_target)

        source_info = layer.connectionProperties['connection_info']
        
        # target_info is connection_info dictionary with new parameters
        target_info = dict(source_info)
        target_info['database'] = db_target
        target_info['user'] = 'gis'
        target_info['password'] = 'gis'

        # setup source and target connection_properties
        source_properties = {'connection_info': source_info, 'dataset': source_dataset}
        target_properties = {'connection_info': target_info, 'dataset': target_dataset}

        layer.updateConnectionProperties(source_properties, target_properties)

aprx.save()
del aprx‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Thanks,
-Joe

curtvprice · ‎05-01-2019

Seems to me like the updateConnectionProperties method on ArcGISProject (the "apex object"?) is the one you want, as that would be a lot less work!

I suggest going back to trying the method on the project, and add some debug code to your script to verify the connection properties are getting modified at all before you bother to .save(). Another thing to try is set the validate parameter of updateConnectionProperties to False and see what happens.

If [validate is] set to True, the connection properties will only be updated if the new_connection_info value is a valid connection. If it is not valid, the connection will not be replaced. If set to False, the method will set all connections to match the new_connection_info, regardless of a valid match. In this case, if a match does not exist, then the data sources would be broken.
(The default value is True)

JoeHershman · ‎05-02-2019

I tried all methods, I only put the sample code for this approach because it is the most granular so I believe as a last effort would show that this does not work. Also my sample has debug code removed as this is not really relevant to what is being done in the functional code. But in all approaches I did as you suggest and had debug code to check if the layer.connectionProperties was changing prior to saving.

Documentation for the method also states:

When working with enterprise geodatabase layers, a path to a database connection (.SDE) file can only be used for the new_connection_info parameter. The information in the .SDE file can't be used for the current_connection_info parameter because the connection information is coming from the layer in the project, not the file. A more complete discussion and examples of connection property dictionaries are also provided in the connectionProperties section below.

Although there is no mention of what should be used as the current_connection_info in this case:

But this has been tried without success

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

project_folder = r'D:\...'
project_file = r'project.aprx'

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

current = 'full path to currect .sde file'
new = 'full path to new .sde file'

aprx.updateConnectionProperties(current, new, False, True)
aprx.save()
del aprx‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I have run inside of Pro and run from Visual Studio, neither working.

Thanks,
-Joe

SrihariDarapaneni · ‎05-02-2019

Few observations I found from you code,

target_info['database'] = db_target ----- this is not setting the property instead it is 
adding additional values to target_info; may be you can use replace method; arcpy 
don't have rich JSON functions. 

this is source connection property  
{'dataset': 'FeatureClass Name, 'workspace_factory': 'SDE', 'connection_info': 
{'authentication_mode': 'DBMS', 'database': 'FeatureClassName', 'dbclient': 'oracle', 
'db_connection_properties': 'database/instance', 'password': '<*********>', 
'instance': 'sde:'database/instance', 'server': 'serverName',
 'user': 'user', 'version': 'SDE.DEFAULT'}}

but target_properties is{'dataset': 'FeatureClass Name, 'workspace_factory': 'SDE', 'connection_info': 
{'authentication_mode': 'DBMS', 'database': 'FeatureClassName', 'dbclient': 'oracle', 
'db_connection_properties': 'database/instance', 'password': '<****>', 
'instance': 'sde:'database/instance', 'server': 'serverName',
 'user': 'user', 'version': 'SDE.DEFAULT'},{'dataset': 'FeatureClass Name, 
'workspace_factory': 'SDE', 'connection_info': {'authentication_mode': 'DBMS', 
'database': 'FeatureClassName', 'dbclient': 'oracle', 
'db_connection_properties': 'database/instance', 'password': '<****>', 
'instance': 'sde:'database/instance', 'server': 'serverName',
 'user': 'user', 'version': 'SDE.DEFAULT'}}

Make sure db_connection_properties,'instance' has to be set properly based on 
database changes. 

And Also

db_source = has to be existing feature class name, 
db_target = has to be target feature class name

most of the time both are same when we doing one database connection to 
other connection. 

- Problem occurring only when you replacing source info to target info values

- more over tool is not giving errors, this may be bug. 

I am able run the tools successfully.

MatthewDriscoll · ‎05-02-2019

This is what I use, first I get the current connection info and then replace it using parameters.

import arcpy


arcpy.SetProgressor("1234", "working on updating sources...")

aprx = arcpy.mp.ArcGISProject("CURRENT")

find_dict = {'connection_info': {'user':'username','password':'userpassword','version':'sde.DEFAULT'},'workspace_factory': 'SDE'}

newuser = arcpy.GetParameterAsText(0)
newpassword = arcpy.GetParameterAsText(1)
version = arcpy.GetParameterAsText(2)


replace_dict = {'connection_info': {'user':newuser,'password':newpassword,'version':version},'workspace_factory': 'SDE'}

aprx.updateConnectionProperties(find_dict, replace_dict,"",False)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

JoeHershman · ‎05-02-2019

Seems that maybe the title needs to be changed to cannot replace the 'database' property. I ran your code switching out the 'user' and 'password' and it works as expected. But it does not work if changing the 'database' property. This was one reason I thought going to the layer level might work better because then I could also change the 'dataset' property to the new fully qualified dataset.

In ArcMap I had a process to script changing the datasource and then to publish the maps, save the copy, and publish feature services to a new server from the new maps. This provided a very streamlined approach to moving from dev to qa to prod. As there are over 20 maps this was a big time saver. At this point we will need to do this manually as it seems one cannot change the database through python

Thanks,
-Joe

MatthewDriscoll · ‎05-02-2019

I see. I think its more important to change the db_connection_properties. Really you should probably change all the connectionProperties when changing to a different databases, it seems you are only changing some of the properties.

Try to find and replace every single property. See the ESRI sample below which includes every property, without joins or relates, I believe.

{'connection_info': {'authentication_mode': 'OSA',                     
                     'database': 'Uhuru',                     
                     'db_connection_properties': 'Dunbar',                     
                     'dbclient': 'sqlserver',                     
                     'instance': 'sde:sqlserver:Dunbar',                     
                     'password': '*********',                     
                     'server': 'Dunbar',                     
                     'user': 'Sly',                     
                     'version': 'sde.DEFAULT'}, 
'dataset': 'Uhuru.SDE.RangerStations', 
'workspace_factory': 'SDE'}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

JoeHershman · ‎05-02-2019

This is the original code with debug output to show exactly what is being used as source and replace. This is what happens running inside Pro. I have moved the print output inline to help, also shown at bottom.

The first print shows the connectionProperties on the layer before running any code.
The next shows the same thing but as variable used in the updateConnectionProperties method
The next shows the replace variable. Unless I am not seeing something this is replacing every parameter just using the new settings.
- The database is replaced with the new database name
- The dataset is replaced with the new fully qualified name of the dataset
The final shows the layer.connectionProperties immediately after the replace operation
I did run with the 'workspace_factory' property but included but accidentally copied this output and too much effort to redo the entire code shot below

After that I break out to simplify and only show the single layer

aprx = arcpy.mp.ArcGISProject("CURRENT")
db_source = 'Staging'
db_target = 'fort_collins'

for map in aprx.listMaps():
    print(map.name)
    for layer in map.listLayers():
        if layer.connectionProperties == None: 
            continue
        print(layer.name)
        pprint.pprint(layer.connectionProperties)
{'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'}

        #replace db name in fully qualified dataset name
        source_dataset = str(layer.connectionProperties['dataset'])
        target_dataset = str(source_dataset).replace(db_source, db_target)

        source_info = layer.connectionProperties['connection_info']
      
        # target_info is connection_info dictionary with new parameters
        target_info = dict(source_info)
        target_info['database'] = db_target
        target_info['user'] = 'gis'
        target_info['password'] = 'gis'

        # setup source and target connection_properties
        source_properties = {'connection_info': source_info, 'dataset': source_dataset}
        target_properties = {'connection_info': target_info, 'dataset': target_dataset}

        pprint.pprint(source_properties)
{'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'}
        pprint.pprint(target_properties)
{'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': 'fort_collins.GIS.PipelineAssembly'}

        layer.updateConnectionProperties(source_properties, target_properties)

        pprint.pprint(layer.connectionProperties)
{'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'}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
        break
    break
Map
Pipeline Assembly

# connectionProperties on layer
{'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'}

#source_properties variable
{'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'}

#target_properties variable
{'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': 'fort_collins.GIS.PipelineAssembly'}

#after update layer.connectionProperties
{'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'}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Thanks,
-Joe

JoeHershman · ‎05-02-2019

I notice I did not set the validate parameter to false. I changed this to

layer.updateConnectionProperties(source_properties, target_properties, '', False)‍‍‍

I am now even more convinced replacing database does not work, because this makes the changes but breaks the connection, because it is not possible to update the fully qualified name of the Feature Dataset through this method.

Using this the new output when evaluating the layer.connectionProperties is what was hoped for

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

Now the dataset is invalid.

I notice a couple things. There is no longer a Database shown in the list after the update. The name of the Feature Dataset is still the old name. However, there is nothing in the connectionProperties that allows one to update the Feature Dataset specifically unless I am not seeing it somewhere.

I am at a loss, I am fully convinced esri did not think of this need but shaking my head if that is the case because to me it seems one of the most useful reasons one would want to change a datasource

Thanks,
-Joe