ArcPy - replaceDataSource (SDE Workspace)

[CraigPerks]

Hi

I'm having trouble with replaceDataSource in a script I've written. The purpose of the script is to iterate through multiple mxds in a directory and replace the data source of certain layers. It works for the most part, except for layers which I'm replacing with a SDE feature class.

The replaceDataSource section of the code is as follows:

for dirName, subdirList, fileList in os.walk(source_dir):
    dest = destPath + dirName.replace(source_dir, '\\')

    for fname in fileList:
        if fname.endswith(".mxd")

            try:
                mxd = arcpy.mapping.MapDocument(dirName + "\\" + fname)

                for lyr in arcpy.mapping.ListLayers(mxd):

                        if lyr.supports("DATASOURCE"):

                            if "\GIS Data\Arc Layers\Example.shp" in lyr.dataSource:
                                newPath = r"Database Connections\SDEConnection.sde"
                                newWS = "SDE_WORKSPACE"
                                newSource = "Example"
                                replace = True

                         if replace == True:

                                try:

                                    print "Replacing data source of " + lyr.name

                                    lyr.replaceDataSource(newPath,newWS, newSource)

                                except Exception, e:

                                     print e

The Exception provided the rather unhelpful message "Layer: Unexpected Error". So I then tried setting the optional "validate" parameter to false and discovered it was doing something quite strange.

The data source of the layer seems to get set to the SDE connection file on a user's C drive (i.e. C:\Users\<username>\AppData\Roaming\ESRI\Desktop10.3\ArcCatalog\SDEConnection.sde) rather than linking directly to instance of the SDE. The user in question isn't even me, I'm assuming it is whoever added the layer to the project in the first place.

What's even more strange is that it successfully changes the data source of all appropriate layers if I just run the script on a single mxd. However when run on a directory with multiple mxds it is only successful the first time but then fails as above the next time it detects a layer to be changed.

I did see this thread, replaceDataSource connecting to SDE Instance, in which the OP seemed to have the same problem amongst others, but this particular issue didn't seem to be resolved by the end of it.

Thanks

[BrianSullivan]

I ran into this too. My first solution was to split out the processing to a first script that walked the directories to find MXDs and then used subprocess.Popen to call second script that accepted the MXD path as an argument . This way the script doing the processing would only run one MXD and then close. The downside is that I had to wait for arcpy to import on every single one, and for 1100 MXDs that adds up.

I'm not 100% sure why the error occurs when you run more than one MXD but I think that replaceDataSource() eats up the connection pool to the target SDE. After processing an MXD or two you can't connect to it any more. The solution is to reset the SDE connection with arcpy.ClearWorkspaceCache_management(). You also should delete the reference to your mxd object first.

A simplified example of your code would look like:

for dirName, subdirList, fileList in os.walk(source_dir):

    for fname in fileList:
        if fname.endswith(".mxd")
                mxd = arcpy.mapping.MapDocument(os.path.join(dirName,fname))

                for lyr in arcpy.mapping.ListLayers(mxd):

                        if not lyr.supports("DATASOURCE"):

                           continue

                        if "\GIS Data\Arc Layers\Example.shp" in lyr.dataSource:
                             newPath = r"Database Connections\SDEConnection.sde"
                             newSource = "Example"

                             try:

                                 print "Replacing data source of " + lyr.name

                                 lyr.replaceDataSource(newPath,'SDE_WORKSPACE', newSource)

                             except Exception, e:

                                 print e

                del mxd

                arcpy.ClearWorkspaceCache_management(r"Database Connections\SDEConnection.sde")