Store Survey 123 Table in local SDE

[jaykapalczynski]

I created a collection in Survey 123 but when it publishes it stores the table and attachements in ArcGIS Online. This is not much of an issues other than the fact that I need to Join this table to data that is on my local SDE environment. I cant seem to figure that part out.

1. Can I create a Relationship Class to a table created from Survey 123 that resides in ArcGIS online to a local Feature Class in my local servers SDE.
2. Can I move the location of the table and Attachments created by Survey123 to my local SDE Server?
   1. Is there some sort of setting that forces the creation of tables etc to my server not ArcGIS Online?
   2. Set a workspace to my server instead of ArcGIS online????
3. Can I create a Relationship class in ArcGIS online that relates the table created in Survey123 (located in AGO) to a feature class on my local server?

[jaykapalczynski]

I created a toolbox and added the script to a new Toolbox tool.  

In that new tool I then defined the 7 parameters at the end of the python script (seen below)

I am not sure what syntax the user needs to put in for the first 5....Can you please help with a couple examples.

sde_conn = arcpy.GetParameterAsText(0)
prefix = arcpy.GetParameterAsText(1)
featureService = arcpy.GetParameterAsText(2)
timezone = arcpy.GetParameterAsText(3)
portal = arcpy.GetParameterAsText(4)
username = arcpy.GetParameterAsText(5)
password = arcpy.GetParameterAsText(6)
process(sde_conn, prefix, featureService, timezone, portal, username, password)
arcpy.SetParameterAsText(7, sde_conn)

[JamesTedrick]

Parameters:

• sde_conn - the SDE connection file to the enterprise geodatabase
• prefix - a small piece of text that will be placed before each table in the survey (i.e., if you enter 'abc' and the table for your survey is called MySurvey, the table will appear in your database as 'abc_MySurvey')
• featureService - the Feature Service url of the survey
• timezone - your timezone (from the list at List of tz database time zones - Wikipedia )
• portal - by default this should be https://www.arcgis.com ; if you're using a Portal to host your surveys, your Portal address

[jaykapalczynski]

Tweaking some parameters and getting this error....

EDIT....changed the SDE_Conn to full path and it got further through he script but getting another error....seems to be with the Sync.  I have the Feature Class in AGO set for sync....dont know where else I might have to set this.

[jaykapalczynski]

Thoughts on this error?

THIS IS CHECKED in ArcGIS Online (the feature class created by Survey123)

Sync X Enable Sync (disconnected editing with synchronization).

Do I have to add my server as a trusted server in ArcGIS Online?

I am blowing up right here but dont know why.  Are there settings on my server that I need to set for Sync Capabilities?  Or is this error coming from a setting in AGO?

if serviceInfo["syncCapabilities"]["supportsAttachmentsSyncDirection"] == True:
replicaParameters["attachmentsSyncDirection"] = "bidirectional"

Start Time: Tue Nov 08 13:31:56 2016
Running script SyncData...
-Getting Replica
======================
FAIL: Downloading Survey
exception:
'syncCapabilities'
['syncCapabilities']
<type 'exceptions.KeyError'>
540
----------------------
arcpy messages:
======================
Failed script SyncData...

Traceback (most recent call last):
File "C:\Users\adminjk\Desktop\PythonSync\SyncBoatRamps.py", line 578, in <module>
main()
File "C:\Users\adminjk\Desktop\PythonSync\SyncBoatRamps.py", line 573, in main
arcpy.SetParameterAsText(7, sde_conn)
File "c:\program files (x86)\arcgis\desktop10.4\arcpy\arcpy\__init__.py", line 670, in SetParameterAsText
return gp.setParameterAsText(index, text)
File "c:\program files (x86)\arcgis\desktop10.4\arcpy\arcpy\geoprocessing\_base.py", line 231, in setParameterAsText
self._gp.SetParameterAsText(*gp_fixargs(args, True)))
RuntimeError: Object: Error in setting parameter as text

Failed to execute (SyncData).
Failed at Tue Nov 08 13:31:58 2016 (Elapsed Time: 2.34 seconds)

[jaykapalczynski]

Update:

I figured out that I had the featureService parameter wrong.  I had to REMOVE the 0 index value at the end of the below string as the script puts it there.

featureService - http://services2.arcgis.com/qsZIVOgnyrsajEtb/arcgis/rest/services/service_mbb5bb/FeatureServer/0

featureService - http://services2.arcgis.com/qsZIVOgnyrsajEtb/arcgis/rest/services/service_mbb5bb/FeatureServer/

But now I have another error....The script runs for some time and actually creates the Feature Class, Relationship Class, and Attachment Table in my EGDB.  BUT it appears to blow when it tries to sync the Attachments...The Records are created in the Feature Class but no images came across  

ANY IDEAS????

ERROR:

FAIL: Updating Tables
exception:
A column was specified that does not exist.
['A column was specified that does not exist.']
<type 'exceptions.RuntimeError'>
556

NameError: global name 'fc' is not defined

Executing: SyncData C:\Users\adminjk\AppData\Roaming\ESRI\Desktop10.4\ArcCatalog\DGIF_TEST@Rack30_Admin.sde BR https://services2.arcgis.com/qsZIVOgnyrsajEtb/arcgis/rest/services/service_vvvb5bb/FeatureServer/ US/Eastern https://www.arcgis.com  xxxx  xxxx
Start Time: Tue Nov 08 16:04:57 2016
Running script SyncData...
-Getting Replica
{"replicaName":"","replicaID":"","submissionTime":1478639100707,"lastUpdatedTime":1478639101433,"status":"ExportingData","resultUrl":""}
-Check 1: ExportingData
{"replicaName":"","replicaID":"","submissionTime":1478639100707,"lastUpdatedTime":1478639109607,"status":"ExportAttachments","resultUrl":""}
-Check 2: ExportAttachments
{"replicaName":"","replicaID":"","submissionTime":1478639100707,"lastUpdatedTime":1478639119697,"status":"Completed","resultUrl":"https://services2.arcgis.com/qsZIVOgnyrsajEtb/ArcGIS/rest/services/service_bcea0ae7dbd646a9b9e7cec73..."}
-Check 3: Completed
-Filtering records to new set
-CreationDate > date '2016-11-08 21:04:58'
featureclass
-Adding Syncronization Time
-Creating Tables
-Creating Domains
-
-Creating Feature Classes & Tables
-Created BR_Boat_Ramp_Inspection_2
-Creating Relationships
-Adding records
-Finding Attachments
-Boat_Ramp_Inspection_2 > BR_Boat_Ramp_Inspection_2
-Field Map
-Boat_Ramp_Inspection_2
-Adding attachments
======================
FAIL: Updating Tables
exception:
A column was specified that does not exist.
['A column was specified that does not exist.']
<type 'exceptions.RuntimeError'>
556
----------------------
arcpy messages:
======================
Failed script SyncData...

Traceback (most recent call last):
File "C:\Users\adminjk\Desktop\PythonSync\SyncBoatRamps.py", line 588, in <module> main()

File "C:\Users\adminjk\Desktop\PythonSync\SyncBoatRamps.py", line 582, in main process(sde_conn, prefix, featureService, timezone, portal, username, password)
File "C:\Users\adminjk\Desktop\PythonSync\SyncBoatRamps.py", line 563, in process cleanup(cleanupOperations, sdeConnection, prefix, now)
File "C:\Users\adminjk\Desktop\PythonSync\SyncBoatRamps.py", line 477, in cleanup arcpy.Delete_management(fc)
NameError: global name 'fc' is not defined

Failed to execute (SyncData).
Failed at Tue Nov 08 16:07:00 2016 (Elapsed Time: 2 minutes 2 seconds)

[JamesTedrick]

The failure with regard to the "NameError" line is a typo in the part of the script that cleans up after failure- fc should be table .

What DBMS is your enterprise GDB?  it maybe rowid (the default name) is reserved and the field got named to something else.

[jaykapalczynski]

SQL Server 2012 R2....I am at a loss here....what do I have to modify?

[JamesTedrick]

Can you look at the tables that were created by the script?  Take a look for any field that is rowid or something that looks like it may have been modified from that (i.e., rowid_1). You can change the default values for the parameters of attachment appending function (line 404) to match that.

[jaykapalczynski]

It appears the attachment table is fine...there are no _1 field names.

The Feature Class on the other hand has 3 fields that have a _1 after them and this is not in the FC in ArcGIS online

Region_1, County_1 and Ramp_1...

[jaykapalczynski]

I dont see a rowid anywhere