Accessing SDE from script published as GPservice in 10.1

[JoshuaKeller]

I am trying to use arcpy and reportlab to generate a report with maps and data tables on a server. Basically the script exports 4 maps (all from the same MXD after setting extent and changing visible layers for each map).

It then creates a feature layer from SDE featureclasses using arcpy.MakeFeatureLayer_management and a selection with arcpy.SelectLayerByLocation_management. It steps through each feature and appends attributes to a list for reportlab to generate the data tables. In the end arcpy.mapping combines all of the interim PDF files into a final multi-page PDF.

This all works from my workstations and generates the reports exactly how I want, however when I try publishing to 10.1 server things stop working. I've fixed most of the problems but am stuck with the SDE featureclasses (SQL 2005) that are directly referenced in the python. Initially I set a string to the path of the feature class "Database Connections\\MyDatabase.sde\\FeatureDataset\\Featureclass" which works perfect from my machine. When it analyzed the tool before publishing the datasource was not registered with the server so I registered it, then published. The GPService fails to run and the error log indicates that it can't find "Database Connections\\MyDatabase.sde\\FeatureDataset\\Featureclass". I then tried moving the .sde file to a static location (C:\temp) on my local machine and the server but now it attempts to copy the entire SDE FC to the server during publish, and won't let me register it again because it is already registered. I removed and re-registered the server and no change. The FC (there are actually 6 different ones) are ~40GB so building the .sd fails.

I thought that when it went through publishing it should have replaced any reference to the registered data source with the servers registered source, but that doesn't seem to be happening or the original configuration should have worked. Any ideas as to how to fix this?

Thanks

[JonathanQuinn]

You're definitely correct in moving the SDE connection file to an absolute path, (C:\Temp), rather than keep it in the Database Connections location.  ArcGIS Server doesn't know what "Database Connections" are, that's something ArcMap resolves as a path.   When you register an SDE database, the location of the database doesn't matter, so it's not going to be a big deal if the SDE connection file gets moved to the arcgisinput folder.  Currently, there's a bug that publishing a geoprocessing service using a python script jumbles up the published script:

NIM084619

So you're saying that publishing the script attempts to move the SDE feature classes to a file geodatabase in the arcgisinput folder?

[JoshuaKeller]

Thank you for the response and verifying that moving the .sde file was appropriate. Part of my confusion was based on the fact that when initially publishing with the Database Connections path it did recognized that the datasource was not registerd and that was cleared up once I registered it, so I assumed it would re-path/copy the .sde file as necessary, however that didn't seem to happen.

Yes, it does appear to attempt to create a fgdb and copy the SDE layers into that however it fails after about 15 minutes I assume due to size (~40gb of data)...staging looks to actually get about 300-500MB in a fgdb but that is corrupt so I can't verify what is in it.

I logged into the server after publishing with the Database Connections path and manually updated the extracted .py file and now it does appear to execute through the rest gpservice endpoint.

My one remaining limitation is figure out how to pass the path to the generated PDF back so the client can download it.

Thanks again,
Josh

[KevinHibma]

My one remaining limitation is figure out how to pass the path to the generated PDF back so the client can download it.

Just output a file (if you're using a script tool) just have an output file parameter and set the file as output.
The framework handles sending the file back.
If your client is ArcMap, it will just get the file for you. A web app, a little bit of code required to get the output:
JS example: http://help.arcgis.com/en/webapi/javascript/arcgis/jssamples/#sample/gp_clipasync

      function completeCallback(jobInfo){
        if(jobInfo.jobStatus !== "esriJobFailed"){
          gp.getResultData(jobInfo.jobId,"Output_Zip_File", downloadFile);
        }
      }
    function downloadFile(outputFile){
        map.graphics.clear();
        var theurl = outputFile.value.url;  
        window.location = theurl;
      }