mxd layer data source replacement for SDE in MSSQL server

159
0
08-26-2020 06:24 AM
IreneEgbulefu1
New Contributor III

Hello Everybody! Please I really need help with this python script. The objective of the script is to list all the mxd files in the specified folder, identify all the layers within the mxd that were sourced from my old SDE database server, and then change the layer source to my new SDE database server.

In other words, the main purpose of the script is to do a bulk data source change for all the layers in my mxd files

So far the script is able to list all the mxds and the paths of the layer sources but it is not changing the data sources for layers in my SDE as needed

import arcpy, os, getpass
newServer = "TNR-GISSQL19-P"
user = getpass.getuser()
#user_dir = os.path.join(u'C:\\Users\\', user)
user_dir = u'Y:\\VIEWERS\\T' ### this specifies the directory containing the mxd files
os.chdir(user_dir)
############create logfile############
logfile = open("logfile_for_Shade_upgrade.txt", "w") ##  creates a logfile

print>>logfile, "User's home directory: %r" % user_dir

############create mxdList############
mxdList = []

for dirpath,_,filenames in os.walk(user_dir):
for name in filenames:
if name.endswith('.mxd'):
mapdoc= os.path.join(dirpath, name)
mxdList.append(mapdoc)
print>>logfile, "------MXD List:------"

############print MXDs to logfile############
for mapdoc in mxdList:
print>>logfile, mapdoc

for file in mxdList:
print>>logfile, "---Searching for SDE connections in: %r" % file
filePath = os.path.join(user_dir, file)
mxd = arcpy.mapping.MapDocument(filePath)
for lyr in arcpy.mapping.ListLayers(mxd):
try:
if lyr.supports("dataSource"):
try:
## if lyr.supports("SERVICEPROPERTIES") and lyr.serviceProperties['ServiceType'] == u'SDE' and 'Server' in lyr.serviceProperties and lyr.serviceProperties['Server'] == u'Frogmouth':
if lyr.supports("SERVICEPROPERTIES") and lyr.serviceProperties['ServiceType'] == u'SDE' and lyr.serviceProperties['Server'] == u'frogmouth':
## print>>logfile, lyr.serviceProperties
print>>logfile, "layer uses Frogmouth"
try:
serverName = lyr.serviceProperties[u'Server']
databaseName = lyr.serviceProperties[u'Database']
if not databaseName.endswith('.sde'):
databaseName += '.sde'
print>>logfile, "Updating: %r, %r:" %(lyr.serviceProperties['Server'], lyr.serviceProperties['Database'])
layerName = lyr.datasetName
print>>logfile, "Layer name: %r" % layerName
if lyr.supports("workspacePath"):
find = lyr.workspacePath ## herein lies the problem, some lyr.workspacePath do not exist
print>>logfile, "Current path: %r" % find
replacePath = os.path.join(user_dir+"\\AppData\\Roaming\\ESRI\\Desktop10.7\\ArcCatalog\\",os.path.os.path.basename(newServer)+"_"+databaseName)
print>>logfile, "New Path: %r" % replacePath
mxd.replaceWorkspaces(find,"SDE_WORKSPACE",replacePath,"SDE_WORKSPACE",False)
print>>logfile, "****************%r has been updated successfully" % lyr.datasetName
else:
print>>logfile, "!!!!!!Connection path not valid! Layer not updated"
except:
print>>logfile, "Unknown error occurred (level 0)."
else:
print>>logfile, "Layer does not use a Vultur SDE connection: %r, %r" %(lyr.datasetName, lyr.dataSource)
except:
print>>logfile, "Unknown error occurred (level 1)."
else:
print>>logfile, "Layer is basemap and does not need to be updated."
except:
print>>logfile, "Unknown error occurred (level 2)."

#mxd.save() ## test first with this commented out, because otherwise you will have to revert to Frogmouth if you want to re-test

logfile.close()
print "Completed."

0 Kudos
0 Replies