bharold-esristaff

Automate Your ETL Processes On A Schedule

Blog Post created by bharold-esristaff Employee on May 31, 2019

This post is about automating repetitive ETL processes right from your desktop.  No code, no server.

 

We're seeing many people using Data Interoperability to periodically synchronize datasets between systems of record.  Typically the source data refresh 'trigger' is driven by a schedule and not some random event, and the frequency of updates is based on multiples of a working day.  If you're on this kind of treadmill this post is for you!

 

You may have heard of this sort of automation in the context of Windows Task Scheduler with a Python script as the task and the script calling a geoprocessing tool or model.

We're going down the task scheduling path too, but without needing Python.

 

In the modern era there is a lot of emphasis on service oriented architecture and the ArcGIS stack has comprehensive publication and synchronization capabilities amongst apps, but you're reading this because you're working outside the stack, at least at one end of your synchronization workflow.  You have used Data Interoperability's Workbench app to wrangle services, databases, files and so on to achieve your own private batch 'service'.  You don't have to be the server and click 'Run' too.  Your friend is this guy:

 

C:\Program Files\ArcGIS\Data Interoperability for ArcGIS Pro\fme.exe

 

That's right, a big fat executable.  This is the one that does all the work when an ETL tool runs.  You may never have noticed, but when you run an ETL tool while being edited in Workbench, the very first line that appears in the log window is:   Command-line to run this workspace:  followed by the path to our new friend above and the path to the open workbench .fmw file, and any arguments the workspace needs.  Its all there, so lets plug it together.

 

Lets dispense with some legalities first.  With ArcGIS Pro, Enterprise and OnLine you're living in a world of named user licensing.  Your ETL tool may embed these credentials.  Provided the scheduled task you build automates the ETL tool on the machine you would use to run it interactively there should not be any licensing issues.  If someone else needs to run it they should replace the named user credentials first.

 

For my example I'm going to recycle an ETL tool example from an earlier post.  I use it to maintain a hosted feature service using data harvested from a Geoserver instance via an extended WFS API.  It has an official refresh rate of once a week, each Saturday local time; I run the ETL tool when I remember to on Monday mornings (hey its only a demo).  Let's automate that.  Mondays are getting problematic for me, I may forget.

 

The example ETL tool reports the command line I should use to run the workspace is:

 

"C:\Program Files\ArcGIS\Data Interoperability for ArcGIS Pro\fme.exe" C:\Work\Synchronize\Synchronize.fmw --API_Key "im_not_telling_you_my_api_key" --LDS_Unique_ID "address_id"

 

Because ETL tools store their parameter values it isn't necessary to supply those arguments if they don't change, so this works too:

 

"C:\Program Files\ArcGIS\Data Interoperability for ArcGIS Pro\fme.exe" C:\Work\Synchronize\Synchronize.fmw

 

Now we create the scheduled task.  Open Task Scheduler and fill in the dialogs for a Basic Task:

 

 

Adjust the settings how you need:

 

 

Tip:  If you configure the task exactly as above a command window like below will pop up, if you don't want this use the setting 'Run whether user is logged on or not'.

 

 

While I remember, if you're interested in more ways to batch ETL check out this post.

 

Now do your bit and come in late Mondays!

 

Note:  We have had reports from the field that Windows Task Scheduler can be impeded from working by some system security settings.  If you find this and cannot work around them with your IT department, log a support call with Esri and ask the analyst to consult Analyst Knowledge Article 000022373 which has a reference to an alternative scheduling technology.

Outcomes