Migrating data from the Survey Feature Service to an Enterprise Geodatabase

30237
224
12-14-2016 12:23 PM
JamesTedrick
Esri Esteemed Contributor
38 224 30.2K

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

224 Comments
IanHamilton2
New Contributor II

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
MVP Frequent Contributor

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
New Contributor III

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
New Contributor III

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
New Contributor

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
New Contributor III

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
New Contributor II

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

MitchJohnson
New Contributor III

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
Occasional Contributor III

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
New Contributor III

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
New Contributor II

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
New Contributor III

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
New Contributor II

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
New Contributor II

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
Esri Esteemed Contributor

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
New Contributor II

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
Esri Esteemed Contributor

Hi Harmond,

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

HarmondSantos1
New Contributor II

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)?

EvanHallein1
New Contributor II

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

RobertWeber
New Contributor III
kmsmikrud
Occasional Contributor III

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
New Contributor III

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
Occasional Contributor III

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
New Contributor

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?