Truncate and Append Issues in 11.1 Portal Hosted Feature Layer

Question

Running Enterprise 11.1 and Python 3.9.11.

We have an on-prem back up for a separate application that handles most of our operational business data stored on an MS SQL Server database, including fire hydrant locations and various associated data. I created a script that accesses the SQL Server and runs a query. That query is read by Pandas and converted to a spatial data frame using the ArcGIS API (from_xy). We then identify an existing hosted feature layer, truncate the only table in that layer, and use the "adds" parameter for edit_features to load the spatial data frame into the hosted feature layer.

We have been able to get this to run and produce the desired outputs on ArcGIS Online, but when I replace our ArcGIS Online org with our Portal in the script, the script runs without any tracebacks and we get the right number of features in the hosted feature layer, but the attribute table has blank rows for all of the features. Even stranger, all of the points are in the right locations, so it's clearly able to read the data in the X and Y columns, but even those columns show up empty in the attribute table. When troubleshooting, I've gone through and run print commands on each of the intermediate steps, and at no point is the data missing from the dataframe until the edit_features() tries to append the data to the hosted feature layer.

Resetting the ArcGIS connection to ArcGIS Online from Portal makes it work as expected once again.

Any help would be appreciated. Thanks in advance.

ChrisStiwinter1 · Answer

Yeah, I was trying to use truncate/ append without overwriting the service each time.  The truncate works fine by accessing the feature service through Python API and accessing the web payers of the service, then use manager.truncate.   From here, i have issues with the append at 10.8.1.   All the parameters of the service are set to be able to utilize the append feature but it fails.  I am working through it, I think i am missing something on the append parameters maybe.

rrhee_bcfpd · Answer

I found a workaround here with some modifications, not sure if it applies to your workflow, and may need some modifications if the service definition tags are different for 10.8.1 from 11.1, but I've posted how I got the solution from the link to work below.

It uses the arcpy mp module and overwriting the service definition to update the features rather than the python API. One thing to note, I didn't have any luck finding a way to modify the SyncEnabled service definition tag, so automating updates to a layer that needs Sync won't work without digging in deeper than I was willing to. Hopefully this helps move you in the right direction.

sddraftloc = os.path.join([output folder], "WebUpdate.sddraft")
sdloc = os.path.join([output folder], "WebUpdate.sd")
prj = arcpy.mp.ArcGISProject(r"[APRX file path]")
mp = prj.listMaps()[[index in maps list]]
arcpy.SignInToPortal("[Portal URL]", '[portal Username]','[portal Password]')
server_type = "HOSTING_SERVER"
sddraft = mp.getWebLayerSharingDraft(server_type, "FEATURE", '[service name]')
sddraft.summary = "[Portal Item Summary Text]"
sddraft.description = "[Portal Item Description Text]"
sddraft.tags = "[Portal Item Tags seperated with comma]"
sddraft.useLimitations = "[Portal Item Use Limitation Text]"
sddraft.portalFolder = "[Folder in Portal(name, but ID might work?)]"
sddraft.allowExporting = True [or False]
sddraft.overwriteExistingService = True [or False]
sddraft.exportToSDDraft(sddraftloc)
arcpy.server.StageService(sddraftloc, sdloc)
arcpy.server.UploadServiceDefinition(sdloc, server_type)

ChrisStiwinter1 · Answer

I having similar issues in 10.8.1 Enterprise.  truncating and appending seems clunky.  I can run an append and the data will show in the target feature service, but the table is empty.  I actually re-ran the tool again thinking that it failed the first time, but now there are 2 features showing when I ID the feature but the table is empty.  Also seems that if layer(0) in the service is truncated then Python cant even return the featureserivce.layer items.  Ive looked around and tried a few different example scripts that other users have created but nothing is working so far.   Sure would be nice for ESRI to put out some sample scripts for truncating and appending a feature service in Enterprise, because I am stuck on this one.  Should be straight forward but is not.

Also, tried to update the web layer using zipped .fgdb published to portal (again ESRI says this works, but does not).