Migrating data from the Survey Feature Service to an Enterprise Geodatabase

[JamesTedrick]

As you may know Survey123 for ArcGIS creates its own feature service when creating a survey. This exists in either ArcGIS Online's or your Portal's Hosted Data. From there, the data can be downloaded as a File Geodatabase easily. This makes it possible to import the data into a geodatabase, though this works best as a one-time import. To help synchronize an enterprise geodatabase with an on-going survey, we're introducing a Python Script and ArcGIS Toolbox Tool, available on ArcGIS Online. You may choose to use this as a one-type migration or as part of an ongoing data synchronization routine.

This script does the following:

• Log in to ArcGIS Online and download the survey database
• If needed, create the tables in the enterprise geodatabase to store the data
• If applicable, filter out records that have already been synchronized over
• Copy over the data from the database maintaining attachments and relationships

This was authored with ArcGIS Desktop 10.4, but will work with ArcGIS Desktop 10.3 if the pytz python module is installed.

Parameters used by the tool

The following parameters are needed:

• SDE Connection File | File Geodatabase: Either a .sde file that connects to the destination database or a File Geodatabase
• Table Prefix: The tables are created will have the prefix applied before the name of the table (i.e., if the prefix is 's123', a table in the service with the name 'myform' will be imported as 's123myform'). This is to prevent the chance of accidentally overwriting tables of the same name. This needs to be unique per survey.
• Feature Service URL: the url to the Feature Service endpoint. This is the endpoint for the Service, not an individual layer (i.e., it will end with '/FeatureServer' with no numbers)
• Time Zone: Dates in ArcGIS Online are stored in UTC time; python processes by default without a time zone. The time zone parameter is needed to calculate the difference. The time zones are specificed in this list. 
• Portal: either 'https://www.arcgis.com' for ArcGIS Online or the Portal's url
• Username: your username for ArcGIS Online / Portal
• Password: your password for ArcGIS Online / Portal. If you don't supply it, you'll be prompted by the script after it starts

If you run the geoprocessing tool in ArcMap and are already signed into to your ArcGIS organization, the script will use that information instead of the Portal, Username and Password parameters.

How to use the script

There are three ways to use the script:

• ArcGIS Script tool: This provides a graphical interface to the tool using ArcGIS Desktop. If you have an enterprise login, first sign into ArcGIS Online or your Portal for ArcGIS, within ArcGIS Desktop.
• Command line: Invoked with parameters specified. The usage in this case is:
  python syncSurvey.py <SDE Connection File | File Geodatabase> <Table Prefix> <Feature Service Url> <Time Zone> <Portal> <Username> [Password]
• Using a config file: The parameters above can be stored in a text file and read by the script, making automated syncronization jobs easier. To run in this mode, use the following syntax:
  python syncSurvey.py CONFIG <path to config file> <section name>.
  The config file has the following format:

[SECTION_NAME]

portal: https://www.arcgis.com

username:

password:

service_url:

sde_conn: <The path to a .sde file that connects to the destination DB OR a File GDB>

prefix: s123

timezone: US/Pacific

[LarryWiebe]

Hi James,

I bumped up my Timeout to 120 and then 360. Both times I received a memory error:

TIMOUT =120

-Check 359: ExportingData

{"replicaName":"","replicaID":"","submissionTime":1535395559090,"lastUpdatedTime

":1535398122933,"status":"ExportingData","resultUrl":""}

-Check 360: ExportingData

{"replicaName":"","replicaID":"","layerServerGens":[{"id":0,"serverGen":0},{"id"

:1,"serverGen":0}],"submissionTime":1535395559090,"lastUpdatedTime":153539932898

0,"status":"Completed","resultUrl":"https://services2.arcgis.com/QoeQkfdOG126FqS

i/arcgis/rest/services/service_fe6714ea3cd845e7a639eca726a260c6/FeatureServer/re

plicafiles/00f7b1e2d55142d3b16414c7151441b2.zip"}

-Check 361: Completed

-Temporary Directory: c:\users\lawiebe\appdata\local\temp\tmpmwcod6

======================

FAIL: Downloading Survey

exception:

out of memory

553

[LarryWiebe]

Hi James, @ James Tedrick

I am hitting an issue where duplicate attachments are downloaded for a feature that has been edited in AGOL.  Tracking is on in the Feature Service and 1 change was made.  I used the syncSurvey.py to create and download the data into a File GDB.  When I identify a point, I get one feature, but the attachments are duplicated.  I assume they are coming from the historical record in the Feature Service. 

I would expect the tool to download the Feature Service to the fGDB with no historical records and only the latest attachments for the feature since history/archiving is not available in the fGDB.

Am I missing something or uncover an issue?

Thanks!

[JamesTedrick]

Hi Larry,

Just to check, looking at the feature's attachments (say, through the Data tab in ArcGIS ) only shows 1 attachment?  If so, I'm not sure quite what's happening - it sounds like a quirk of the createReplica endpoint, which is what I'm using to download the data.

[LarryWiebe]

Hi James,

This is a very basic dataset at the moment and only has 1 inspection, with 6 attachments and that is how it shows in the data tab in AGOL:

But in ArcGIS Desktop 10.5 connected to the GDB exported using the syncSurvey.py I get 1 feature with 12 attachments:

As you can see, there is a dup of each attachment...

Thanks,

Larry

[JamesTedrick]

Hi Larry,

Does the duplication occur when downloading a fgdb via the Export options on the Item Details page?

[LarryWiebe]

Hi James,

Just did a download, the attachments are not duplicated…

Larry

[LarryWiebe]

Hi James,  Were you able to look into this further?

Thanks, Larry

[AndyBlackman2]

Hi Brian,

Thanks for your comment and workaround, I've been having the same issue with duplicated attachments. Have you noticed any issues with leaving that line commented out in the long run, or come up with a new workaround?

Thanks

Andy

[ThomasColson]

Will this be ported to Pro Python (3.x)?

[Peters_Amy]

Hi James,

Same issue as Andrew Hargreaves. I ran the tool for the first time today in ArcCatalog successfully but I had the same issue where the schema came through and the relationship class, but there was no data in the two feature classes in the fgdb.  We don't have Pro yet.

Thanks,

Amy

[HaroldHerrera1]

Hi James Tedrick‌

Using Script for the tool box, gives me the following error:

I would appreciate if you can help with this error

[JamesTedrick]

Hi Harold,

Unfortunately, the images aren't supported in responses to blogs...

[HaroldHerrera1]

Excuse me, James.

I attach the image.

[Anonymous User]

Hi James,

I am replying on behalf of a client who is finding that their Survey 123 data, and related table relationships are lost, when running the script to download the subject data from ArcGIS Online. Regarding your above reply - am I correct in interpreting that the subject synchronize survey script should instead be maintaining the existing relationships by using further relate fields, that are added by the script, and used to support the relationships?... And does the script set up the existing relationships between the output datasets ? (-ie. no further interactive steps by the user, to set up the relationship, is required after the script is run? )
Also, I note above (in your posting from Jan 10 2017 9:38am) that you made changes to the script ? Going by the update date (6th Dec 2016) on the downloadable script at : https://www.arcgis.com/home/item.html?id=554fbefb325a4db488a24b0db4e99519 ... Is there a more up to date script available from somewhere else?
Thanks for any clarification that you can provide.

[ThomasColson]

James Tedrick‌ Will this be ported to ArcGIS Pro (Py 3.x)? 

[JamesTedrick]

Hi Allison,

I made extremely minor changes to the script in Jan 2017 - no change in functionality.  Yes, relationships should be persisted across the migration process.  The source is available at GitHub - tedrick/SyncSurvey: Synchronize a Hosted Feature Service to an Enterprise Geodatabase 

[JamesTedrick]

Hi Thomas,

I am investigating what would be required to migrate to Pro.

[Anonymous User]

Thanks very much James, appreciate your quick reply.

Sounds like the Pro alternative will be a popular one.

[AndyBlackman2]

Hi James Tedrick‌,

Firstly thank you for this script, I have been using it for 6 months now and it has enabled an automated workflow to get the data from AGOL into our Enterprise Geodatabase. I wondered how easy it is to ignore attachments in the Feature Service? I have a survey with around 1500 records, with around 3000 attachments. Over the last 6 months they have not been used from the database, and the users are happy to view the images on the Survey123 website. Any help would be much appreciated.

Thanks

Andy

[JoeFlannery]

Andy:

I don't know what your script looks like, but I used this line of code in a python script to keep attachments in an ArcGIS Online hosted feature service from being downloaded to my local database.

arcpy.env.maintainAttachments = False

[ThomasColson]

In the Pro-version of this script, what would be really "Bling" is an option to resize attachments. 

[MeganJohnson]

James Tedrick‌

I am running into a similar issue related to the Creator field. 

When the editing settings of my hosted feature layer in AGO are set to the following, I receive the error message: "Error executing function. An expected field was not found or could not be retrieved properly. [Creator]. Failed to execute (DisableEditorTracking)."

• Enable editing
• Keep track of who created and last updated features
• Enable Sync

When the editing setting as set to the following, I receive a different error message: "FAIL: Preprocess Surveys for transfer exception: Cannot find field 'SYS_TRANSFER_DATE' <type 'exceptions.RuntimeError'> 562

• Enable editing
• Keep track of created and updated features
• Enable sync

Any thoughts on the correct editing settings for my hosted feature layer and how to troubleshoot the error messages? There is a field called Creator present in my feature layer. 

Thanks!

[deleted-user--2hDb5YTmQS1]

Hi James Tedrick‌,

Maybe my case is related to some of the previous questions/statements, asking the script to be upgraded to a newer ArcGIS Pro version. I must say that I am not a python expert, but try to use this script as it is.

My environment is ArcGIS Pro 2.1, ArcGIS Enterprise 10.6, PostgreSQL 9.5

I've been trying to run the script for the first time now and encountered this error message:

Start Time: Donnerstag, 18. Juli 2019 09:09:05
Running script SyncFeatureService...
Failed script Sync a Hosted Feature Service to a Geodatabase...
File "C:\Users\jbi\Applications\ArcGIS\syncsurvey\SyncFeatureService.tbx#SyncFeatureService.py", line 191
print surveyGDB
^
SyntaxError: Missing parentheses in call to 'print'
Failed to execute (SyncFeatureService).
Failed at Donnerstag, 18. Juli 2019 09:09:05 (Elapsed Time: 0,08 seconds)

Since this seems to be a pretty "simple" error I assume that it may be caused by the software versions I use.

Can you help me with this or hint to what I might do to get the script running?

Thank you very much, best regards,

Jürgen

[PeterMacKenzie2]

Given this started over 3 years ago, i'll ask: is this still the best way to replicate between an AGOL Hosted Feature Layer and an SDE Feature Class? Or has recent versions of Pro implemented something like this?
Also, does the sync process bring only new data or records that have been updated since last replication too?

[Anonymous User]

Hi Peter,

We are facing the same issue regarding the sync process that bring only new data (records that have been updated are not transfered). Did you find any solution?

Thanks

Cristian

[PatriciaAlgura]

Hi James Tedrick,

This script will be very useful! And I was also looked into it, but got the same error with line 191. Do you have an updated version of this tool that works with ArcGIS Pro (Python 3.6.6)? 

Thanks

[JamesTedrick]

Hi Jurgen,

It appears you running this with ArcGIS Pro (Python 3.x).  The script was designed to be run from ArcMap (Python 2.x).

[JamesTedrick]

Hi Peter,

I would first ask if it is possible to create the form against a feature service that uses the feature class in the geodatabase - that would be the ideal solution.

This sun script is only designed to bring in new records, not update existing ones.

[JamesTedrick]

Hi Patricia,

Thanks for the feedback.  I have been looking into migrating it to Pro, but do not have it ready at this time.

[DerrickFrese1]

Hi Everyone, and especially James Tedrick‌,  I am getting the following error.  Any ideas?

[JamesTedrick]

Hi Derrick,

First, I would check that the editor tracking field is actually called 'CreationDate' - it might have a different name.

[East_LongmeadowGIS]

Larry,
Did you ever get this squared away? I have had this operation running for over a year without issue until today. With no change to the script, I am encountering the exact same issue.

[MalcolmMeyer2]

I realize this is three years later, but did you find a solution to extract the edited records? If you change "CreationDate" to "EditDate" the tool simply downloads the record again since it is using "append". If not any ideas on how to enable this?

[MalcolmMeyer2]

Looking through the code I cannot figure out why the original objectid does not get populated into the SDE feature class. This would allow for the removal of historical versions edited features. As it stands, if I change the CreationDate to EditDate, I get the edited features, but the only way to tell that this is an updated record of an existing feature is by looking at the CreationDate. This is doable, but having a field that copies over the Survey123 objectid would be very helpful. Thoughts on how to implement this appreciated if anyone has ideas.

[JamesTedrick]

Hi Malcolm,

ObjectId is never guaranteed to be consistent on transfers - it's a value that is always controlled by the database managing the tables and because of that shouldn't generally be used to 'track' features.  As designed, the script provided does not handle edits, as reconciling differences (say, edits made in both tables) requires either business rules that are organization specific or manual processing.

[WilliamWalsh1]

James Tedrick‌ When the script runs and appends data to the enterprise (oracle 12) database, however I am still seeing the Sys_Transfer_Date in UTC.  I referenced the link provided about to ensure I am using the appropriate TZ code in the confguration file, America/New_York, however it seems that the script isn't recognizing that setting.  However, I am not getting any errors. Do you have any advice on how to rectify this?

[WilliamWalsh1]

Did you ever find a solution that will allow you to append deltas as well as new records?

[WilliamWalsh1]

Did you have any luck in resolving this issue?  I know it has been a while since you asked the question but I am still appending data that is in UTC.

Any help would be great!

[RexRobichaux]

Hello James Tedrick‌ and anyone else that might be able to assist. We setup our first Survey123 survey yesterday using the web editor on Portal  10.7.1 as a hosted feature service. Ultimately, this data will need to be synced / reside on our Oracle enterprise geodatabase. I tried to get the tool running within ArcGIS Desktop, and am making progress. It seems to be almost there now, but is hitting an issue when trying to create the necessary feature class in Oracle given the RDBMS name identifier constraint. It seems like instead of grabbing just the feature service table name (survey), it's grabbing the entire feature service / Portal item name which is way to long to be an Oracle object name- see error below: 

Is there an easy way to edit the script to confine the output name to only "EDAS123_survey" as intended and drop the remaining characters from the feature service name? 

Thanks to anyone who can assist...this one might be a simple python edit.

Best-

Rex

[JamesTedrick]

Hi Rex,

You may be able to work around this by creating a lookup of Survey123 service names and desired table names or by truncating the table name during the initial read.

[JohnPina]

Does this script require a higher than basic license?  I get the following error.

ERROR 999999: Error executing function.
The application is not licensed to create or modify schema for this type of data
Failed to execute (AddField).

[JamesTedrick]

Hi Juan,

As you are editing an enterprise geodatabase, you need a standard Desktop license.

[abdullahqasrawi]

I Couldn't understand what is the table prefix exactly, could you please clarify it more?

[JamesTedrick]

Hi Abdullah,

The 'Destination Prefix' is a prefix code you want to specify to note that these were tables that have been imported from the feature service.  It was put in to minimize the chance of someone accidentally overwriting their main business tables.

[abdullahqasrawi]

Thanks, now I am facing the following error

 

Any advice

[JohnPina]

Thanks James.  I am using a file geodatabase as the destination workspace but I am getting same error. Also, I can't seem to connect to ArcGIS Online. I get the following error when portal url is set to www.arcgis.com.  I think it has to due with sync capabilities note being enabled on feature server.

FAIL: Logging in to Survey
exception:
'capabilities'
['capabilities']
<type 'exceptions.KeyError'>
545

[JamesTedrick]

Hi John,

Yes, Sync needs to be enabled on the feature service.

[JamesTedrick]

Hi abdullah,

This script was meant to be run in a Python 2.x/ArcMap environment, not a Python 3.x/ArcGIS Pro environment.

[abdullahqasrawi]

Hi James,

Thanks for your help, I used ArcMAP and there is no error appears but in the same time the results did not sync to my Gdb

[JamesTedrick]

Hi abdullah,

Can you review the messages from the GP output and confirm there were no errors?  I can't necessarily tell form your screenshots which feature classes are present in your ArcMap map.