Replace Data Source

MaryM · ‎03-14-2013

I try this code and then my path changes to the path of the mxd plus the sde connection. I am not sure I am going in the right direction anyways? I am trying to change all my SDE files that point to one server to another with the same feature datasets and names. Please Help.


#Set Your Variables Up Here!
#   Must add prefix r (stands for raw) and put path in quotes. 
targetMXD = r"\\filepathhere.mxd"
userName = "me"

#   Set variables for creating new SDE Connection
folderName = "C:/Documents and Settings/" + userName + "/Application Data/ESRI/Desktop10.0/ArcCatalog"
fileName = "Connection to NewSDETest.sde"
serverName = "SP56"
serviceName = "5151"
databaseName = "gis"
authType = "DATABASE_AUTH"
username = "look"
password = "look"
saveUserInfo = "SAVE_USERNAME"
versionName = "SDE.DEFAULT"
saveVersionInfo = "SAVE_VERSION"
#Process: Use the CreateArcSDEConnectionFile function
mxd = arcpy.mapping.MapDocument(targetMXD)
for df in arcpy.mapping.ListDataFrames(mxd):
    for lyr in arcpy.mapping.ListLayers(mxd):
        if lyr.supports("SERVICEPROPERTIES"):
            servProp=lyr.serviceProperties
            sdeFile= '"'+folderName+'/'+fileName+'"'
            if (lyr.serviceProperties["ServiceType"]=="SDE") and (lyr.serviceProperties["Service"]== "5153"):
                dataSet=lyr.dataSource
                list=dataSet.split('\\')
                newDataSet=str(list[2])
                currentName=newDataSet.split('.')[0]
                newDataSet.replace(currentName,userName)
                lyr.replaceDataSource(sdeFile,"SDE_Workspace",newDataSet,"")
                print lyr.dataSource
arcpy.RefreshTOC()
arcpy.RefreshActiveView()
mxd.save()
del mxd

TonyAlmeida · ‎03-14-2013

Do you get an error when you run the script? if you do what is it?

MaryM · ‎03-15-2013

My error is that my MXD changes the path of the SDE layer to this in the Layer Properties, Source Tab, Data Source:

Data Type: SDE Feature Class
Feature Class: puts feature dataset here
Location: puts folder of mxd + sde connection path here
Feature Dataset: feature dataset name
Feature Type: Simple
Geometry Type: Polygon

What I need to do is change the SDE path and the feature datasets first word which is the name of the SDE database. I only need a certain SDE database so I do not want to just change all in a MXD, that is why I was going layer by layer.

MaryM · ‎03-15-2013

So excited! I got it to work! This is the full script, I cannot post my work's data path's up here so I changed them out, if they look a little funny....

#Data Replacement, by: rangermry
#3/13/2012
#Script to replace data sources in a MXD
#Written using Python 2.6.5 and the ArcGIS Desktop 10.0 ArcPy Mapping Module.
#This script makes a new SDE connection file on the user's C Drive and then replaces the old server's paths in the MXD with the new
#connection file.

#Import Python Libraries
import arcpy, os
#Set Your Variables Up Here!
#   Must add prefix r (stands for raw) and put path in quotes. 
targetMXD = r"\\servername\mxdpath\.mxd"
userName = "rangermry"

#   Set variables for creating new SDE Connection
folderName = "C:/Documents and Settings/" + userName + "/Application Data/ESRI/Desktop10.0/ArcCatalog"
fileName = "Connection to Server.sde"
serverName = "SERV1"
serviceName = "5151"
databaseName = "gis"
authType = "DATABASE_AUTH"
username = "local"
password = "local"
saveUserInfo = "SAVE_USERNAME"
versionName = "SDE.DEFAULT"
saveVersionInfo = "SAVE_VERSION"
#Process: Use the CreateArcSDEConnectionFile function
rcpy.CreateArcSDEConnectionFile_management(folderName, fileName, serverName, serviceName, databaseName, authType, username, password, saveUserInfo, versionName, saveVersionInfo)
mxd = arcpy.mapping.MapDocument(targetMXD)
for df in arcpy.mapping.ListDataFrames(mxd):
    for lyr in arcpy.mapping.ListLayers(mxd):
        if lyr.supports("SERVICEPROPERTIES"):
            servProp=lyr.serviceProperties
#Replace old dataset name with new dataset name and replace
            if (lyr.serviceProperties["ServiceType"]=="SDE") and (lyr.serviceProperties["Service"]== "5153"):
                dataSet=lyr.dataSource
                list=dataSet.split('\\')
                newDataSet=str(list[2])
                currentName=newDataSet.split('.')[0]
                newDataSet=newDataSet.replace(currentName,username)
                newDataSetName=newDataSet.split('.')[0]
                sdeFile="Database Connections\\"+fileName
                lyr.replaceDataSource(sdeFile,"SDE_Workspace",lyr.name,"")
arcpy.RefreshTOC()
arcpy.RefreshActiveView()
mxd.save()
del mxd

MichaelVolz · ‎03-15-2013

Mary:

When you run this python script on your system, do you have the SDE connection password in blank text for the creation of this new SDE connection?

If you, would it not be better to use a SDE connection file from another location that has already been created so the password is not contained within the python script?

MaryM · ‎03-15-2013

The way our system runs is that we have a general username and password server that is out for all our users to see. Then we have another server that we do all our work on and it replicates over to the general user server. The private server people would need to put their own username and passwords in but that is for our GIS staff so we know what to change.