Need advice on efficient work flow to update online map by pulling data from SQL database

[LeighErik]

I am working with ArcGIS Pro 2.8.3 and ArcGIS Online.  

We have an SQL server that contains a database with contact information that I'm interested in using as a data source for an online map. I have been able to gain permission to access the specific database with said contact information via an OLE DB connection, but I do not have any other permissions or direct access to the server.  I can't build anything on the server and I publish maps and apps through ArcGIS Online. Network security will not allow computers outside the network to establish a connection to the database so any map hosted off site will not be allowed direct access.

Our partners are able to update their own database contact information through a web interface with the server.

My goal is to find an efficient way to integrate the database into a workflow to update an online map with less human involvement. I currently have a test map built in ArcGIS Pro that contains a polygon layer joined to the database.  This layer is then exported as a new feature layer to embed the data to remove the local file connections.  I then customized popups with telephone links, website links, google map address links, etc, and it works nicely, but I had to build it as a completely separate layer since ArcGIS Pro wont publish a map online with local file connections.  

I'm trying to get some input to determine if this is the most efficient work flow.  It seems like there should be a better way of doing this that doesn't require so much work to publish updates. I'm mostly self taught so I feel that I'm likely unaware of many useful methods.  Please don't hesitate to suggest ideas or ask questions.  You won't offend.  Thanks!

[jcarlson · Accepted solution]

The Python API would be helpful, I think. We have a similar situation getting assessment data from a protected, on-site DB into our parcel layers on the web.

As long as you have a common field in your hosted layer and the source DB, you can update the data easily enough. Here's a simplified version of our script:

from arcgis import GIS
import pandas as pd

# query local DB
connstr = 'connection string goes here'
sql = 'sql query goes here'

df = pd.read_sql(sql, connstr)

# query hosted layer
fl = gis.content.get('itemid of hosted layer').layers[0]

hosted_df = fl.query(
    as_df = True,
    out_fields = ['objectid', 'key field from local DB'],
    return_geometry = False
)

# merge tables together
out_df = df.merge(sdf, on='key field from local DB')

# submit queried attributes as edits
i = 0
while i < len(out_df):
    fs = out_df.iloc[n:n+100].spatial.to_featureset()
    updt = fl.edit_features(updates=fs)
    i += 100

[LeighErik]

Thanks for the input Josh.  I was able to use similar python script to update the arcgis online attribute tables with data from our microsoft sql server.  Took a bit to learn some of these different methods.  

[jcarlson]

Glad to hear it! A bit of time investment up front is to be expected, but hopefully it saves you plenty in the long run! The time I've saved from scripts like this can be measured in days at this point.