How To Make Your ArcGIS Spatial ETL Web Tools "Talk FME"
We do our best to automate things but sometimes you need to wire up FME-technology parameters with Esri geoprocessing for yourself. ModelBuilder is your friend.
Here is my example, an ETL tool process needs help from a couple of Calculate Value model tools. Let's see why.
A customer asked how to build a web tool to power a Web AppBuilder geoprocessing widget, it had to work a certain way. Excel files needed to be uploaded and zipped CSV data had to be returned, not just one zipped CSV file per Excel input file but multiple CSV files in each zipfile. Okay.....no map in sight, just my kind of job!
You can pretty quickly stub out a Spatial ETL tool that applies any business logic you need to go from Excel to CSV (the two most popular non-spatial dataset types in the ETL world by the way). But, what about zipping them up and returning the zipfile as an output?
FME technology has some paradigm differences with ArcGIS. FME Workbench tends to work with what the Esri world thinks of as workspaces - folders and databases. When you configure an ETL tool you supply input and output datasets accordingly, but to the ArcGIS geoprocessing environment everything looks like an input parameter. How do you get data out for downstream geoprocessing or as a result in an app like Pro? The simplest way is with ModelBuilder as part of the web tool.
If your workflow is writing to a geodatabase then put your ETL tool in a model and add a Select Data model tool to extract the features you want. You'll then have geoprocessing outputs available.
For my example I had to get a little more creative. The first challenge wasn't around parameter types, it was zipping multiple CSV outputs per input. Many formats in FME technology support conversion to a zipfile. The way to do this with CSV data is supply a zipfile name as the last part of the output dataset "folder". Here is my ETL tool workspace:
And here is my output "folder":
If you look closely at the workspace you'll see I trigger a feature type fanout based on an attribute value - see the CSV File Name is an expression @Value(Environment). This gives me one output CSV file per unique attribute value. In the writer parameters I specify a path ending in "Output.zip". In operating system terms it is a file but to Workbench (and ArcGIS when calling the ETL tool) it is still a folder.
To make everything work as a web tool I need correct ArcGIS parameter types, folder input and file output. Folders do not work as outputs. I also want to specify a scratch file location for the output so that the job environment can see and return the data. The trick is to wrap the ETL tool in a model and set paths and parameter types explicitly.
The two Calculate Value model tools are key.
The first one creates a zipfile path in the job scratch environment but casts it to Folder:
The second one takes the same path and casts it to File:
Then everyone is happy. I can run the tool in Pro, it uses the local scratch environment, then I can share the result as a web tool and it will use the ArcGIS Server job scratch environment at run time.
I get my data back in Pro and use Web AppBuilder against the web tool.
Simple, isn't it! Now you can build your Spatial ETL web tools into your enterprise geoprocessing workflows.