MXD Layers Assigned To Temp SDE Files When Using ArcPy & ArcCatalog

[Clint_Glover]

I am currently responsible for updating layer data sources within mxds after an Oracle password change takes place.  To do this I have put together a Python script that goes through a folder (designated by the user), finds all of the .mxd files, and one by one reads in the layers of each mxd (arcpy.mapping.ListLayers), checks the workspacePath of each layer to determine which Oracle account is used for the layer, and then uses the replaceDataSource method to set it to the updated sde connection file.  After it finishes, it saves the mxd and then goes on to the next mxd file.  

I've recently discovered that when running the Python script, the data source information of some of the layers will occasionally be assigned to a temporary sde connection file that is being generated by some part of the ESRI software.  This assignment is being done BEFORE my Python checks the workspacePath and attempts to use the replaceDataSource method.  The temporary sde files are being generated in the following location on my local machine: C:\Users\John Doe\AppData\Local\Temp.  Within the Temp folder, a folder titled "arc66D2" was generated, which contained the temporary sde file that was named 131dcb16a392ebc818a09874d51fce89.sde.  When I looked at the connection properties of this temp sde file (from ArcCatalog), it contained the old connection information from one of the layers within a mxd I ran my Python script on.  This behavior has happened on multiple occasions, each of which involves a different temporary sde file that were generated.

I have also discovered that this behavior occurs some times when I open ArcCatalog, right click on a mxd file, and select the "Set Data Source(s)..." option, which allows you to manually update the data source information for an mxd.  The tricky part is that there doesn't appear to be any pattern with this occurrence.  Has anyone ever had this same issue and/or know why this is occurring and how to prevent it?  From my tests, this has occurred when working with mxds on my local machine as well as mxds located on various servers.

After a password change, I have to run this Python script on thousands of mxds that have been created over the past 5 to 10 years by our end users to fix the data source information of all the layers, and when this temporary sde file is thrown in there "randomly", I can't trust my script to work 100% of the time and it causes additional work for me.  

For reference, I am using ArcMap/ArcCatalog 10.6.1 and Oracle 12c.  In the past, our Oracle account passwords have never changed, thus this wasn't a problem.  Recently, my organization started forcing us to update our Oracle passwords every 90 days, so this is becoming a huge ordeal for our GIS group to support and manage.

[ChaseSchieffer]

Clint,

I'm having the same issue, although I'm using SQL Server. Were you ever able to find a solution?

[Clint_Glover · Accepted solution]

Hey Chase!  It has been extremely difficult to pinpoint the cause of this issue.  I had worked with ESRI tech support for a few weeks awhile back and we both could not figure out the reasoning behind it.  What I ended up doing was putting in an additional if statement in my Python script to check a layer's workspacePath for keywords (like 'APPDATA', 'LOCAL', 'TEMP', '.SDE') that would match that odd temporary SDE file path.  Once I had that layer captured in that if statement, I assigned it to one of our updated SDE files.  This meant all of those temporary SDE paths would end up being reassigned to the same SDE file.  This isn't ideal because I would like for them to match up with the account used previously, but it was the best solution I could come up with.  Hopefully this helps!

[ChaseSchieffer]

Thanks for the info Clint. Fortunately, I was able to find a different way to change the data sources. Most of our map files were created by adding the same layer file to each map, so all the layer names were the same in the TOC for every map. So I was able to search each map for a specific lyr.name, and then use lyr.replaceDataSource to change the data source (I just had to write out the instructions for each of the 30 or so feature classes). I should also point out that both the old SDE database and the new SDE database had the same schema. I was simply moving from an old SQL Express database to a full enterprise SQL database.