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

[IanHamilton2]

Hi James, you mentioned that this script would be available on ArcGIS Online as a toolbox, where would I find it.

Also would it be available on Portal? We put our Survey 123 on our Portal.

Finally, is it intended to update the script to work with ArcGIS Pro?

Thanks,

Ian Hamilton

[JamesCrandall]

We had been running this as a scheduled task on an 2012R windows box and is now failing. Here's the last print statement:

{"replicaName":"","replicaID":"","layerServerGens":[{"id":0,"serverGen":0}],"submissionTime":1588943947580,"lastUpdatedTime":1588944574083,"status":"Completed","resultUrl":"https://services1.arcgis.com/sDAPyc2rGRn7vf9B/ArcGIS/rest/services/TrackerHistory/FeatureServer/repl..."}
  -Check 63: Completed
 -Temporary Directory: c:\users\jcrandal\appdata\local\temp\22\tmpz2orp_
======================
FAIL: Downloading Survey
exception:
File is not a zip file
('File is not a zip file',)
<class 'zipfile.BadZipfile'>
595
----------------------

[MitchJohnson]

Hi James,

I am currently trying to troubleshoot this same issue as a python novice.  I am scratching my head at the field name issue - isn't this script creating a brand new table?  I am only defining a workspace using the tool in ArcMap so why would the creation date field be something I need to change and how do I do that?  I do not have an existing table yet as this is only referencing a feature service in our portal.

Thank you!

Mitch

[MitchJohnson]

I figured out where to change "CreationDate" to "created_date" as i was doing it in the python script outside of the geoprocessing tool originally.  Now I am stuck at a domain assignment error.  I'll take another stab tomorrow - but i am wondering if I should try to find a different solution to link inspections back to a feature class automatically as we will eventually move to ArcGIS Pro.

Thanks,
Mitch

[JamesMitchell8]

Hello,

I am also get the error The application is not licensed to create or modify schema for this type of data. The feature layer have sync enabled and i am exporting to a file gdb.

Any ideas how to resolve?

Thank you

[DionO_Reilly]

Hello James, I am getting Error 000358: Invalid Expression (MakeFeatureLayer).  I am running Desktop 10.7.1.  Any suggestions would be appreciated.  Thank you

[SurveyAdmin]

Hi Mitch, did you get any resolution here?  I am getting the same message and not sure why.  

[MitchJohnson]

Hi Survey Admin‌,

Unfortunately, no I did not get a resolution with this.  I decided to take a different approach and generate survey from an existing feature service.  Using a relationship class, I am able to inspect a feature on Collector causing it to open up Survey123 and automatically generate our Manhole ID in the associated survey.  This is in an enterprise geodatabase using portal for Collector/Survey123.

Best,

Mitch

[kmsmikrud]

Hi James Tedrick,

Thanks so much for the great tool. It was recommended as a solution for bringing down survey data on a regular basis. I've just started testing it and I'm getting an error that I think has been mentioned regarding attachments. Currently the service has three layers in it with attachments enabled on the one layer but currently there are no attachments. I'm not sure what the AttributeError: 563 is, but if you could offer any advice it would be much appreciated. Thanks in advance, Kathy 

[DionO_Reilly]

I was wondering if this issue was ever resolved?  I am generating the same error on a survey that has a repeat table and attachments.

[HarmondSantos1]

I also ran this today and got this error. Anyone has an idea what could have caused this?

Check 79: ExportingData
{"replicaName":"","replicaID":"","layerServerGens":[{"id":0,"serverGen":0},{"id":1,"serverGen":0}],"submissionTime":1598515975440,"lastUpdatedTime":1598516802167,"status":"Completed","resultUrl":"https://services2.arcgis.com/Z0aWguFDHESYteHT/ArcGIS/rest/services/general_inspection/FeatureServer/..."}
-Check 80: Completed
-Temporary Directory: c:\users\hsa191\appdata\local\temp\arcd2d4\tmpoxkxrq
======================
FAIL: Downloading Survey
exception:
File is not a zip file
['File is not a zip file']
<class 'zipfile.BadZipfile'>
558
----------------------

[RobertWeber]

I am running the script using python 3x.  The main things I ran into has to do with urllib2. In Py3 needs to be imported a little differently.  

from urllib.request import urlopen 
from urllib.parse import urlencode‍‍‍

Then of course remove anything using urllib2.

also where urlencode is being used it needs to be explicity encoded as bytes or 'utf-8' (at least the error I got was a 'TypeError' where the parameters for the url were being treated as string.)

urlencode(replicaParameters).encode("utf-8")‍

I did not really have any other issue that I can remember aside from those differences in using urllib....

Thanks again for all your work on this James this script has held up over the years!

[HarmondSantos1]

Hi James Tedrick‌,

Thank you very much for this tool! I'm hoping to implement this as a scheduled task to send across data to an SQL database from the AGOL Survey 123. I'm test running it to connect to a local GDB and I'm getting this error message.

It has created a table in the destination workspace but fails to copy across the rows.

I have made changes to the code such as "returnAttachments" = False as I'll be only be needing the table to be copied across.

Hoping you or any one from the community can provide guidance with me on this. Thank you very much in advance!

Best regards,

Harmond

[HarmondSantos1]

Hi, just want to ask if you were able to get around with this? I'm hoping to automate bringing the AGOL data to an SQL database without the attachments as well.

Thank you,

Harmond

[JamesTedrick]

Hi Harmond,

The error indicates that something did not allow the destination tables to made correctly.  One possible workaround would be for you to create the tables in the enterprise geodatabase.  Otherwise, you should troubleshoot with your DBA as to why the table creation is failing.

[HarmondSantos1]

Thanks, James Tedrick‌! I was able to get around the issue. Your comments in the python code really helped me debug and configure to suit our needs. The code is running successfully when connecting to a local fgdb and an enterprise gdb. I'm getting 'DBMS table not found' when trying to connect to a non enterprise GDB  (MS SQL db)

Will probably coordinate with the database administrator to debug this one.

Thanks, again!

[JamesTedrick]

Hi Harmond,

The script was designed to write into eGDB Feature Classes, writing into non-eGDB tables will likely not work at all.

[HarmondSantos1]

Hi James,

Oh no. That's bad news. Would have any idea if there's a way to sync Survey 123 table to non-eGDB tables (if it's even possible)?

[Anonymous User]

Any chance you can post your python 3 version somewhere? Thanks 

[RobertWeber]

https://community.esri.com/docs/DOC-15393-sync-feature-service-to-sde-py3x 

[kmsmikrud]

Hello GRSM GIS and James Tedrick,

I am running into the same issue noted above with existing tables in an enterprise database and I was wondering what worked for getting it to update when prior tables exist. When I first run the tool, I specified a prefix of YukonPSS_long and that works great. I've never gotten the tool to run successfully again.  In the prefix I've tried several options including sde_dcf.DBO.YukonPSS_long, DBO.YukonPSS_long, and YukonPSS_long. In the response above you mention ((i.e., GIS.GRSM_SASQUATCH_SRV_EVENT - this should be found by the search pattern) or capitalization (GRSM vs grsm). What do you mean by search pattern? 

Since you said you had made it past this issue I was curious what worked hoping for some insight.

Thanks in advance,
Kathy

[RobertWeber]

Not certain this will solve your issue but for a prefix be sure that your prefix itself does not include an underscore '_'  since this is being added by the script after whatever prefix you choose.  The fact that it is saying the tables already exist should not cause the tool to fail it should instead check the existing feature class and filter existing features.  

[kmsmikrud]

Hi Robert!

Thanks for the reply! I didn't have an underscore at the end of the prefix BUT your reply prompted me to think about the prefix I was including with the underscore. Then I looked at the script and found this:

for t in allTables:
tableName = t.split('.')[-1]
nameParts = tableName.split('_')"

So I removed the underscore and renamed my tables in the enterprise geodatabase and reran the tool. It worked!!!!! I need to check it all but at first glance I've never gotten the tool to repeat and sync (I'm so excited!). I wouldn't have thought of the underscore in my naming but that was it. Thank-you!!

[ChristiGabriel1]

I was using this script without issue, but now I have some new surveys (created with 123 v3.11.123) and the script is no longer functioning correctly. It is changing the globalid of the point feature class that my repeat tables are linked to. Anyone have a work around for this since I am using the globalid/parentglobalid fields to link my repeaters to my non-repeater?

[NancyMe]

Hello @JamesTedrick , Is there any version of this script that I can run it in ArcPro?

Thank you.

[MARIEL-U]

i tried this solution but it's not working in ArcGIS Pro 2.8.0 (Python3).   some of the errors displayed were

1. ModuleNotFoundError:  No Module name 'urllib2;

2.  Module 'urllib' has no attribute 'urlopen'

3. Module 'urllib' has no attribute 'urlencode'

4. POST Data should be bytes, an iterable of bytes, or a file object.  It cannot be of type str.

i tried to fix it but  didnt work.

[jfiacco]

Hi @JamesTedrick !

Thank you so much for developing this script. 

I've developed a Survey with 784 fields. When running the script, I get the following error.

I've tested the survey using fewer fields. The script runs with 599 fields but crashes with anything greater. Does anyone know a work around for this? I need to include all 784 fields for this survey. Any help would be greatly appreciated! Thanks!