Migrating data from the Survey Feature Service to an Enterprise Geodatabase

65379
227
12-14-2016 12:23 PM
JamesTedrick
Esri Esteemed Contributor
38 227 65.4K

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

227 Comments
JamesTedrick
Esri Esteemed Contributor

Hi Marc,

Is there any other message addressing the error?  There are a couple of places Editor Tracking is disabled.

MarcCavallaro
New Contributor III

HI James, no there aren’t any other errors. Just the one I posted.

Thanks.

JamesTedrick
Esri Esteemed Contributor

Hi Marc,

I believe I know where the script is failing; the problem is that I don't know why.  I would need to know a lot more about your survey to be able to understand why it fails.  Was this a survey created through Connect or the Survey123 website?  What is the name of the survey table in the feature layer?

bengtdjuvfeldt2
New Contributor

We have found this script very useful, but we have run into one issue. It seems to us that coded values are resulting in domains in the target SQL database för the main layer. However, coded values for the related tables (repeats) are not resulting in domains in the database. Is this a limitation or are we missing something in how we have implemented the script?

PankajJamwal1
New Contributor II

Thanks for the script. I was wondering if this script works with ArcGIS for Collector data too? If yes, what should I use on Table Prefix parameter? If not, Do you think if there is any synchronization tool available for Collector data?

Thanks  in advance.

GrantCrosswell1
New Contributor

Thank you James, its a fantastic script.  Saved reinventing the wheel and is more comprehensive than we would create.

If you are doing any updates, would you please consider unique domain naming. I cannot see if any one has mentioned it already but I'd also had a problem where a domain from a previous survey was preventing creation of a new survey from another schema user into a SQLServer Geodatabase.

It would be useful if the domain name could be set to be more unique across the geodatabase, e.g. schemauser_survey domain name.

The error that was returned when the survey was synchronized is :

======================
FAIL: Making Tables
exception:
ERROR 000464: Cannot get exclusive schema lock. Either being edited or in use by another application.
Failed to execute (TableToDomain).

['ERROR 000464: Cannot get exclusive schema lock. Either being edited or in use by another application.\nFailed to execute (TableToDomain).\n']
<class 'arcgisscripting.ExecuteError'>
562

Thank you

Kind regards

CAICAdmin
New Contributor

James Tedrick‌ 

1.  maybe i'm missing something? Am I correct that this tool will not seem to support feature service (FS) realtionshipclass where PrimaryKey = Globalid.  I Found that the GPtool fails when trying to create relationshipclasses with globalid/non-globalid field (looks to be a known issue) can do it manually in UI.  Also since tool relies on Append,  these data loading tools assign new GlobalID values to the appended rows and therefore do not preserve the original GlobalID values. 

2.  Should line 482 in cleanup() read 

arcpy.Delete_management(thisName)

  instead of 

arcpy.Delete_management(view)   i don't see "view" definition

CAICAdmin
New Contributor

Issue is caused by gptool  CreateRelationshipClass()  does not support  globalid/ non-globalid relationship. 

Looks to be a known issue in desktop and but can create it manually through the UI.

RyanDeBruyn1
New Contributor III

I found similar issue with domains. The SDE workspace I was copying to already had existing domains with the same name.   I was able to work around the failure by trapping the error and passing on the arcpy.TableToDomain_management() call.  When the new table is created, since the domain already exists there are no issues with the field properties.

PankajJamwal1
New Contributor II

Hi,

Thanks for the script.

Does this script work with Collector for ArcGIS Data too? I have some feature services on AGOL which I need to store in FGDB. If yes, then what should we enter in Table Prefix parameter?

TIA

PJ

JamesTedrick
Esri Esteemed Contributor

Hi Panakj,

Yes, the data will work with data collected from Collector; the only requirement is that the 'Sync' capability is enabled on the Feature Layer.  You can use whatever you want for the prefix- that was put in place just to decrease the likelihood that an existing table would be getting overwritten by the script.

PankajJamwal1
New Contributor II

Thanks a lot. It worked

MorganTitus1
New Contributor II

Hi James,

I am getting an error when running the script right away about not being able to connect to AGOL, so I'm assuming my feature service rest endpoint is wrong.  Where is the best place to get that link?  I am using the Service URL from MyContent --> Overview and it looks like this: https://services.arcgis.com/sz087R4yBGLjbAdY/arcgis/rest/services/service_e243c302edd045c4a91375e046...

This link is not working though.  I am using the geoprocessing tool through arcmap and am signed into my AGOL account in ArcMap.  Any suggestions?

Thanks,

ThomasColson
MVP Frequent Contributor

Any chance this script will be updated to work in Pro Python 3.x?

JamesTedrick
Esri Esteemed Contributor

Hi Thomas,

You're right- I should update this to support Pro.  That being said, it may take a bit of time before I can get to it.

BretLian
New Contributor II

I am getting this same issue. Initially, the script runs and creates all the tables and populates all of them except for the attachments. Subsequent runs of the script populates data where it should in all tables except for the attachments. Anybody come to any resolution with this issue?

ThomasColson
MVP Frequent Contributor

" You can use whatever you want for the prefix- that was put in place just to decrease the likelihood that an existing table would be getting overwritten by the script." Is there a way to disable this? I want the results to go into an existing table, e.g, we might change the name of the survey from year to year, or want to pump multiple surveys (same schema, different workgroups) into the same table. 

JamesTedrick
Esri Esteemed Contributor

Hi Thomas,

Yes, this can be disabled by modifying the python file.  Look for all occurrences of 

"{0}_{1}".format(prefix, table)

 

and replace with just

table

Assuming the table names in the feature service are the same as the names in the eGDB.

ThomasColson
MVP Frequent Contributor

That didn't work for some reason. Have an existing SDE table "GRSM_AA_SITE_FORM_2018", that is the name of the feature service layer, same exact schema, 

FAIL: Making Tables
exception:
ERROR 000258: Output RESTON_VEG_MAP.sde\GRSM_AA_SITE_FORM_2018 already exists
Failed to execute (CreateFeatureclass).

['ERROR 000258: Output RESTON_VEG_MAP.sde\\GRSM_AA_SITE_FORM_2018 already exists\nFailed to execute (CreateFeatureclass).\n']
<class 'arcgisscripting.ExecuteError'>
562
----------------------
arcpy messages:

ERROR 000258: Output RESTON_VEG_MAP.sde\GRSM_AA_SITE_FORM_2018 already exists
Failed to execute (CreateFeatureclass).

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

C:\temp\SRV_2018>

JamesTedrick
Esri Esteemed Contributor

H Thomas,

I can see the issue - the search for the survey tables depends on the prefix.  In your case, you might be able to either hard code the results (if doing this for just one set of tables; you would edit line 547 to assign the tables) or change the search from the prefix to the table name (modify line 547 to change the reference from prefix to the name of the table).

ThomasColson
MVP Frequent Contributor

You might have to translate that for me.....hard code the results...so do I go back to 

"{0}_{1}".format(prefix, table) 

and hard code GRSM_AA_SITE_FORM_2018? Where do I put the repeat question table, which in this case, is titled "repeat_species_event", names by the Survey Connect app when I published the survey. 
ThomasColson
MVP Frequent Contributor

So line 547 is: 

if len(existingTables) > 0:
lastSync = getLastSynchronizationTime(sdeConnection, existingTables)

Not sure where to hardcode the two table names in there....

ThomasColson
MVP Frequent Contributor

Any update on this from October of 2017? I'm getting the same error for the same reason. 

"

Thank you James, its a fantastic script.  Saved reinventing the wheel and is more comprehensive than we would create.

 

If you are doing any updates, would you please consider unique domain naming. I cannot see if any one has mentioned it already but I'd also had a problem where a domain from a previous survey was preventing creation of a new survey from another schema user into a SQLServer Geodatabase.

 

It would be useful if the domain name could be set to be more unique across the geodatabase, e.g. schemauser_survey domain name.

 

The error that was returned when the survey was synchronized is :

======================
FAIL: Making Tables
exception:
ERROR 000464: Cannot get exclusive schema lock. Either being edited or in use by another application.
Failed to execute (TableToDomain).

['ERROR 000464: Cannot get exclusive schema lock. Either being edited or in use by another application.\nFailed to execute (TableToDomain).\n']
<class 'arcgisscripting.ExecuteError'>
562

 

Thank you

Kind regards"

ThomasColson
MVP Frequent Contributor

Ryan DeBruyn‌ Are you able to share your PY script where you solved this error? I'm having a hard time with trapping the error and passing on the arcpy.TableToDomain_management() call. 

"I found similar issue with domains. The SDE workspace I was copying to already had existing domains with the same name.   I was able to work around the failure by trapping the error and passing on the arcpy.TableToDomain_management() call.  When the new table is created, since the domain already exists there are no issues with the field properties."

ThomasColson
MVP Frequent Contributor

To answer my own question, after several hours of grief:

try:newDomain = arcpy.TableToDomain_management(tempTable, 'CODE', 'DESC', outWorkspace, domainName, update_option='REPLACE')
except Exception: print 'Domain Exists'‍‍‍
GrantCrosswell1
New Contributor

I'd found that if I renamed the domain to a new generic name in the script, that subsequent running of the scripts would find this new name and not try to re add again if it existed.  Also if your prefix has an '_' there are script parts that delimit using this character to create table names e.g. the '__ATTACH' table name.

ScottKichman
New Contributor III

Hello all, before I dig in,,does this work for any hosted feature service? ie., FGDB published to AGOL for Collector  consumption?

Thanks!

JamesTedrick
Esri Esteemed Contributor

Hi Scott, 

Yes, this script should work for any hosted service - there isn't anything special about Survey123 in that sense.

ScottKichman
New Contributor III

Thank you, I was getting errors with the date field, I will send a screen

shot if thats ok

DougBrowning
MVP Esteemed Contributor

This happened to me.  Date is a reserved word.  Change it to something like FormDate. 

ScottKichman
New Contributor III

Thanks! I keep forgetting about those reserved words!

FrancisCorbett
New Contributor II

Hi James Tedrick‌ I've tried the script in ArcMap 10.6, Pro 2.1.2 and cmd line, but I am getting the same ssl error:

Error snippet

I'm running the script on a feature service against our own Portal with "Allow access to the portal through HTTPS only." ticked.

I'm wondering if I need to modify the script and import an SSL site package to get this to work with our Portal?

Thanks for the script and in advance for any help.

ThomasColson
MVP Frequent Contributor

how is you're portal "certed"? signed by a CA? If not, you'll need to add the root cert of however you signed it to the urllib request. 

JamesTedrick
Esri Esteemed Contributor

Hi Francis,

Are you using this script with ArcGIS Online or a Portal?  When referring to the URL, you are using https, correct? Do you know if your workplace has an SSL proxy on outbound traffic?

FrancisCorbett
New Contributor II

Hi tpcolson - thanks for your reply. Yes, Portal is CA-certified.

FrancisCorbett
New Contributor II

Hi JTedrick-esristaff‌. Using the script against Portal, which does begin with https. We do have a proxy for outbound traffic. However, I understand traffic should not be going outside our network. I also tried substituting the non-web-adaptor name for Portal address.

JamesTedrick
Esri Esteemed Contributor

Hi Francis,

I you are using this against a Portal's hosted feature store, does the portal have a self-signed or enterprise-signed certificate?  That would also lead to SSL errors within Python (which doesn't use the same certificate list as your web browser).

TaraBoswell
New Contributor III

Hi James,

So we had the tool working perfectly when we had switched to publishing the survey without globalids and we were syncing to fGDB on the network. We have a new setup though and we need to sync to an enterprise geodatabase (Oracle ArcSDE environment). When I try to use the sync tool now though I get this error:

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

FAIL: Updating Tables

exception:

An expected Field was not found or could not be retrieved properly. [ROWID]

['An expected Field was not found or could not be retrieved properly. [ROWID]']

<type 'exceptions.RuntimeError'>

575

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

The only thing that has changed is that we are pointing the sync tool to our SDE instance instead of a network fGDB, and we definitely didn't get this error before. I had tried directly copying our fGDB into SDE and got an error message saying that ROWID is a protected field name in Oracle. Wondering if that could be the issue here, that Oracle isn't liking the use of that field name and so won't let us put the data in?

I wasn't sure whether the ROWID field is coming from the survey (when I set it to publish without globalids) or your script. Decided to test out the former scenario. I republished my survey WITH GlobalIDs enabled after seeing your response that if the Preserve GlobalIDs environment setting is checked on then the sync tool should work. I got error messages saying The value is not a member of GlobalID though. I looked into it more and it appears that the Preserve GlobalIDs setting is currently only in Pro, and your sync script doesn't run in Pro.

I'm at a loss now as to how to get our data into SDE. We were really relying on the ability to use your script to sync down the data/updates. Any thoughts?

TaraBoswell
New Contributor III

James Tedrick,  Just want to add that I took a copy of the sync tool and went through and changed every instance of ROWID to TEMPROWID and then ran the script on the version of our survey without globalids and it worked......except that the relationship between the image attachment tables and our survey points did not come through properly. The relationship classes between the other feature layers and tables all seem to be working though. Any idea what could be going on?

JamesTedrick
Esri Esteemed Contributor

Hi Tara,

It sounds like your survey was published prior to last november and did not use global IDs to maintain the relationships; in that situation Survey123 created a field called 'ROWID' to store the guid.  This has since been changed to 'UNIQUEROWID' to accommodate Oracle DBMSes, which have ROWID as a reserved term.  As to the attachments not maintaining their relationship, I am unsure, but it could be that the renaming caused a problem with the attachment relationship.

TaraBoswell
New Contributor III

Actually Survey123 did create the "UNIQUEROWID" field (just has an alias of RowID) that it was using to store the GUID and all that worked fine. The only place I could see an actual field of Rowid getting created is in the Sync script and when I renamed that, the sync to Oracle DB worked. 

The wierd thing is that I just went through all the tables/layers and relationship classes for the image attachments (these all use GlobalID and Rel_GlobalID by default) and the values DID match up...the values of the Rel_GlobalID in our s123_Rareobs_Attach table DID actually have a corresponding match in the GlobalID field of the s123_Rareobs point layer. I thought maybe the values were getting changed and that's why the relationship wasn't working. All the info in the relationship class looked correct. On a lark I (in a copy of the data) I deleted the relationship class and remade it, looking exactly as the other did, and suddenly now the relationship is working. Tomorrow I'll test recreating the two relationship classes I'm having a problem with in our actual SDE copy of the data and run the Sync again and see if everything comes down properly. Can't explain why this happened but I'm hoping this will resolve it. So odd!

TaraBoswell
New Contributor III

James Tedrick

So I can confirm that when downloading our survey data from AGOL directly, everything works perfectly and the image attachments are there. With both runs of the Sync Script (to file geodatabase and enterprise geodatabase) though, as I describe above, the attachment table relationships didn't work until I deleted them and added them again (named exactly the same and with identical settings). All seemed well until I went to open some of the photos and while it looks at first like everything is there, no photos are actually showing. I see the file name and the attributes but no ability to open and see the image.

I had already put in a ticket with ESRI and when the technician and I were working on the issue, he had me try to run the sync again, this time to a fresh file geodatabase. Now suddenly every run of the script creates a new error (see below). He suggested posting here to you to get your input. 

EDIT: I figured out the error message -> I forgot I had modified the script with a line we needed for input to the correct workspace in SDE and so when I ran it again but to file geodatabase on the network, it threw errors.  So we are good on the error message but still curious if you've heard of anyone else having issues with images not coming down with attachment tables? I reran the script just down, to come down to fGDB and again, all the parts are there but the relationships with the attachment tables aren't functioning/being recognized, even though they are present and appear correct, and I can't access the pictures themselves.

SMW_GIS
New Contributor III

Thanks for the creating this tool James. It has been extremely helpful for us in ArcMap.  We're migrating a lot of our workflows to Pro.  Any update on this?  

TaraBoswell
New Contributor III

Hi Brian,

We've just realized that this is happening with our data as well. I think the duplication was there for awhile but it wasn't caught. This is on first time sync of the data down from AGOL. Were you ever able to figure out what the issue was and get a resolution?

Thanks,

Tara

AndrewHargreaves2
Occasional Contributor III

Hi James Tedrick‌,

I'm attempting to run this from the Toolbox in Pro and receiving the below error:

Start Time: Tuesday, August 14, 2018 6:14:31 PM
Running script Sync a Hosted Feature Service to a Geodatabase...
Failed script Sync a Hosted Feature Service to a Geodatabase...
File "C:\Users\andrewha\OneDrive - puretechltd.com\Documents\GIS\Howard County\syncsurvey\syncsurvey\SyncFeatureService.tbx#SyncFeatureService.py", line 191
print surveyGDB
^
SyntaxError: Missing parentheses in call to 'print'. Did you mean print(surveyGDB)?
Failed to execute (SyncFeatureService).
Failed at Tuesday, August 14, 2018 6:14:31 PM (Elapsed Time: 0.05 seconds)

If I attempt to run this in ArcMap it throws no errors but fails to download anything.

JamesTedrick
Esri Esteemed Contributor

Hi Andrew,

The script was written against Python 2.7 (ArcMap), not Python 3.0 (ArcGIS Pro). I need to update the script sometime in the future.

AndrewHargreaves2
Occasional Contributor III

Hi James Tedrick

If I attempt to use this in ArcMap it completes without error, however I get nothing created in my fGDB.

Thanks

LarryWiebe
Occasional Contributor

Hi Scott, Did you ever get this to work without having Catalog logged into AGOL?  I am hitting the same issue right now...

Thanks,

Larry

LarryWiebe
Occasional Contributor

Hi Gang, and thank for the tool and great support!

 

I am trying to use the tool to pull down a large feature service (28k features and a repeat for collecting photos with 108k photos) for the first time.  I have ArcGIS 10.5.1 and I am using a Batch file to launch the python script, it runs for about 20-30 minutes, then gives me a token error.  The last portion of the screen out is below.  I added some timers to the batch file for reference…

 

I also added a comment in earlier today further up the chain that I have to have my Arc Catalog running and logged into AGOL for this script got past the initial connection to AGOL, even though my credentials are correct and case sensitive in the config file.  

 

                -Check 163: ExportAttachments

{"replicaName":"","replicaID":"","submissionTime":1535140801527,"lastUpdatedTime":1535140890720,"status":"ExportAttachments","resultUrl":""}

                -Check 164: ExportAttachments

{"replicaName":"","replicaID":"","submissionTime":1535140801527,"lastUpdatedTime":1535140890720,"status":"ExportAttachments","resultUrl":""}

                -Check 165: ExportAttachments

{"replicaName":"","replicaID":"","submissionTime":1535140801527,"lastUpdatedTime":1535140890720,"status":"ExportAttachments","resultUrl":""}

                -Check 166: ExportAttachments

{"error":{"code":498,"message":"Invalid token.","details":["Invalid token."]}}

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

FAIL: Downloading Survey

exception:

'status'

['status']

<type 'exceptions.KeyError'>

553

----------------------

arcpy messages:

 

 

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

.

. Script started at 14:59:39.39

. Script  ended  at 15:29:03.27

.

. Script took 1770 seconds to complete

.

Press any key to continue . . .

JamesTedrick
Esri Esteemed Contributor

Hi Larry,

That's interesting - based on the setting in the getToken function, it should have a life of 60 minutes.  One thing you could try is to simply increase the value of the 'expiration' value to give the token a longer life.

About the Author
I work on the Survey123 team. Please note that I do not use the private messages feature in Esri Community; if you need to reach me, feel free to e-mail jtedrick@esri.com .