Change the datasource from Query Layer to feature layer

[AnjuPaul]

Hi, 

I have a map document which I have imported in ArcGIS Pro. This document contains about 150 query layers. I wish to change the data source for these query layers to a registered view in my database. I was able to do this manually in pro using the Set Data Source functionality. But since there are lot of layers, it is a huge task to update it manually.

I tried the layer.updateConnectionProperties(), but it doesn't seems to work.

Here is my code: (Note I have manually changed the data source for the third layer so that it acts as a source to apply the data source to the remaining layers)

doc=arcpy.mp.ArcGISProject('current')
map=doc.listmaps()[0]
lyr = map.listLayers()[2]
conProp = lyr.connectionProperties
for lyr in map.listLayers():
    if lyr.supports("dataSource"):
    cp = lyr.connectionProperties
    if cp is not None:
        if(not cp['dataset']=='MY_VIEW'):
            lyr.updateConnectionProperties(cp,conProp)

Any help would be appreciated.

[MobiusSnake]

I could be wrong but I don't think this is possible.

Some years back (during the ArcMap days) I worked on a project where we were modifying a large number of MXDs containing query layers, I found that ArcPy was very limited in how it could interact with query layers.  As a result I ended up writing a bunch of ArcObjects code.

As far as I know ArcPy support for query layers hasn't been improved, and ArcObjects doesn't work with Pro.  You might be able to do something with the Pro SDK but I'm not that familiar with it.

Alternatively - and this is a bit of a hack - you might be able to unpack your APRX and parse the internals with an ArcPy script.  If you rename your APRX to be a zip file and unzip it, I think you'll find a bunch of files (XML I think?) with all your layer properties, you might be able to read that XML and create feature layers based on what you find in there.  I haven't tried anything like this however, just a crazy idea.

[AnjuPaul]

Thank you for the reply. 

I have tried exporting to mapx file and edited the data source property in it by copy pasting the ones from the manually changes layer. But it doesn't seems to work either.

[AnjuPaul · Accepted solution]

I just got this working. This is not the proper way but still do what I want. I executed the script by setting the validate option as False in the updateConnectionProperties method. This will break the connection for the layers in my ArcGIS Pro project and then set the data source manually by selecting on one of the broken red exclamation connections. It will repair the data source for all the layers. Job Done!!

The updated script is given below just in case it help someone else.

doc=arcpy.mp.ArcGISProject('current')
map=doc.listMaps()[0]
lyr = map.listLayers()[2]
conProp = lyr.connectionProperties
for lyr in map.listLayers():
    if lyr.supports("dataSource"):
        cp = lyr.connectionProperties
        if cp is not None:
            if(not cp['dataset']=='MY_VIEW'):
                lyr.updateConnectionProperties(cp,conProp,validate=False)