Single Edits SQL Server: Push Data from the Database to an Application in Real-Time with Webhooks

Introduction

FME Flow (formerly FME Server) can respond to database events in real-time by making use of external database triggers. Create a real-time data pipeline between your database and external applications with FME Form (formerly FME Desktop) and FME Flow. 

In this example, we show you how to set up SQL Server and FME to respond to single feature database edits. A user will be able to submit database edits to street data from an FME Flow App that is synchronized with an ArcGIS Online Feature Service, all facilitated by FME. In summary:

• Use an FME Flow App to load data edits into SQL Server.
• An SQL Server Trigger pushes the edit data to an FME Flow Automation. 
• The Automation pushes the SQL Server database edits to an ArcGIS Online (AGOL) feature service.

Requirements

SQL Server 2019
ArcGIS Online 

Step-by-Step Instructions

The instructions break the workflow down into its major sections and the steps are best carried out in the order it is written in. Download the tutorial resources (sqlserver-single-edits.zip) from the Files section of this article to use the workspaces and scripts referenced in the instructions. An FME Flow project file is also available to upload the workflow to your FME Flow instance. All components will need to be modified to your system specifications.

Part 1: Create an SQL Server Database

First, we will create an SQL Server database and table to use in the example. The following steps use the Microsoft SQL Server Management Studio (SSMS) application to perform all tasks.

1. Create a New Database
Open up your SQL Server. Create a new database, name it “fme-single-edits” to follow our example. 

2. Edit the Query
Load the Create_Table.sql query. You will need to edit it to your own specifications. Ensure that you replace all occurrences within the script (this would be a good job for the “Find and Replace” function!).
a. Database name. The example database is named “fme-single-edits”, keep this if you’re following the example. 
b. Database User. The example is “dbo”. This refers to the system user schema, not the login name. 

3. Examine the Query
Let’s look at what the query is doing. Step 1 creates the table that will receive and record edits. 

Steps 2 and 3 grant user permissions and enable Ole Automation procedures.

4. Execute the Query and Review
A SINGLE_EXAMPLE table will be created upon a successful query result. 

Part 2: Loading Edits

This section will configure an FME Workbench workspace (sqlserver_single_loader.fmw) that will be run by an FME Flow App . The workspace will receive user edits, process them, and send edits to the SQL Server SINGLE_EXAMPLE table created in Part 1. 

1. Read in the Data
This workspace uses a Shapefile Reader to read Streets.shp. You may recognize its schema from the SINGLE_EXAMPLE table.


2. Filter the User Selection
The workspace User Parameters offer choices for a user to submit edits to SQL Server. The choices in this example are relatively simple, with a static selection and operation, but they could easily be made to your own specification. 

The workspace filters the selection by operation (INSERT, UPDATE, or DELETE) with TestFilters. AttributeCreator’s assign a database operation attribute (fme_db_operation) that informs the Microsoft SQL Server Spatial writer what operation to perform and a WHERE clause to select the correct row (fme_where). 

3. Connect to SQL Server
Update the Microsoft SQL Server Spatial writer with your own SQL Server Database Connection. Make sure the new connection is not a Published Parameter in Manage User Parameters. 


Open up the writer’s parameters, within Table > General, ensure that the fme_db_operation attribute is used as the Feature Operation. Under Row Selection, set the WHERE Clause to the fme_where attribute. 


4. Test the Connection
Run the workspace. In SQL Server, the edit will appear as a new row in the table. We have successfully sent an edit to SQL Server from our workspace!


5. Upload to FME Flow
Using the Publishing Wizard, upload the sql_server_single_loader.fmw workspace to a specified folder (e.g. “SQLServer-Single”) in your FME Flow. Ensure to upload the database connection and Streets shapefile if they do not already exist on FME Flow.

Part 3: Create an FME Flow App 

This section will create a user-friendly FME Flow App that will run the loader workspace. Users can submit edits to SQL Server from a web application without ever typing out a query. 

1. Create a Flow App
In FME Flow, expand Flow Apps then select Create Workspace App. Create a new app that runs the sqlserver_single_loader.fmw workspace.


2. Configure Flow App Permissions
After creating the app, click on the “Add More Permissions'' link.

On the Flow App’s Token management page, allow Access to the SQL Server connection. 

Part 4: Logging Edits

This section first creates the ArcGIS Online (AGOL) Feature Service that will record our SQL Server edits. After, we configure an FME Workbench workspace (sqlserver_single_logger.fmw) that will receive webhook data from SQL Server, process it, and send the edits to the AGOL feature service. If you do not have access to AGOL, proceed to Step 2 and use the workspace’s alternate Esri Shapefile writer. 

1. Create a Feature Layer in ArcGIS Online
From your ArcGIS Online account, create a Feature Layer with Line geometry. 


From the Data > Fields tab of your new Feature Layer, add four new fields: id, RowAction, DateEdited, and Username. 


2. Read and Process Data
Open up sqlserver_single_logger.fmw in FME Workbench. The workspace parameters represent the JSON keys that will be sent from SQL Server to FME, as seen here in the ParameterFetcher.


The GeometryReplacer translates the SQL Server geometry into geometry we can send to AGOL. The DateTimeStamper creates a date attribute to record the time of edits. 

3. Connect to ArcGIS Online
Update the existing AGOL writer’s Web Connection and Feature Service with your own, or replace it by adding a new Esri ArcGIS Online (AGOL) Feature Service writer to the canvas (for guidance with creating an AGOL connection, check out these troubleshooting steps). Open up the writer’s Parameters… and connect to the Feature Service you just created. Set Layer Definition to “Import from Dataset…”

 
 
From the Parameters tab, set the Geometry… to arcgisonline_polyline. 

From the User Attributes tab, format the following attributes to be sent to AGOL. 


4. Map Workspace Attributes to Writer
Whether you’re using the AGOL writer or the Shapefile writer, ensure that the workspace attributes are being written to the corresponding writer attributes. 

5. Publish to FME Flow
Using the Publishing Wizard, upload the sql_server_single_logger.fmw workspace to your project folder (SQLServer-Single). Ensure to upload the AGOL Web connection (and shapefile, if you’re using that) if it does not already exist on FME Flow.

Part 5: Create an Automation and SQL Server Trigger 

Now that our users can submit edits from an app, we want to automatically synchronize the edits with an ArcGIS Online feature service. In this section, we will create an SQL Server Trigger that will push edits to an FME Flow Automation. The automation receives the edits and runs the logger workspace that updates the ArcGIS Online Feature Service. 

1. Start Building the Automation
Create a new Automation in FME Flow. For now, just add a Webhook Trigger and save the Automation. After saving, open the Webhook details and copy the Webhook URL to your clipboard. You will need this URL in the next step. 


2. Edit the Query
In SQL Server, load the Create_Trigger.sql query. The following will need to be edited to your system configuration: 
a. Replace the webhook's URL (@sUrl) with the URL copied from your clipboard. Leave the ‘?’ at the end of the URL. 
b. Database name. The example database is named “fme-single-edits”, keep this if you’re following the example. 

3. Examine the Query
Step 4 creates the stored procedure (HTTP_DB_MESSAGE) that facilitates the HTTP call from SQL Server. The HTTP call pushes information from SQL Server to the FME Flow Automation’s webhook trigger. The content of the message (@message) is created in the database trigger (Step 5).


Step 5 creates the database trigger that, for each edit, tests for the database operation (INSERT, UPDATE, DELETE), selects the target rows and formats the content into JSON. The trigger fetches the rows from the temporary inserted and deleted tables. An additional system variable, SYSTEM_USER, records the user’s login name. 


4. Execute the Query and Review
Execute the query. The Object Explorer now lists a table, trigger and stored procedure as pictured below.


5. Configure the FME Flow Webhook Trigger
Back at the FME Flow Automation, open up the Webhook Trigger Output Attributes (formerly Output Keys). Add operation, objectid, geometry, and username as separate Webhook Attributes to receive and parse the JSON message from the SQL Service webhook. 

You may notice that these match the SELECT statement from Step 5 of our SQL Server query. This is where you can customize what information to send from SQL Server.

6. Add a Run Workspace Action
Attach the sqlserver_single_logger.fmw workspace as the Run Workspace action following our Webhook trigger. From the drop-down menu, expand Webhook, and select the matching webhook attributes to pass the SQL Server message into the logger workspace’s parameters. 


Save and start the automation. 

Part 6: Run the Workflow 

Now that we have configured each component to our specifications, let's see the workflow in action. 

1. Submit Edits
Open up your FME Flow App and choose an edit to send to SQL Server.

2. Check your ArcGIS Online Feature Layer
If all goes well, your updates will be reflected in the Feature Layer. The following is an image of the Data tab for my Feature Layer, with some additional operations. 

In the Feature Layer’s Map Viewer, inspect the feature’s geometry in context.


Now you have a workflow that submits, records, and synchronizes edits in SQL Server... all powered by FME! 

Troubleshooting

If the Flow App does not complete successfully (e.g. the job fails):

• Check the FME Flow Job log for the sqlserver_single_loader.fmw workspace. Are there any errors?
• Check that the API token has access o your SQL Server Connection. If your connection is not listed, upload the connection with your workspace. 

If the Flow App completes successfully, but there are no updates in your AGOL feature layer, there are several places to check the workflow: 

• Check the FME Flow Job log for the sqlserver_single_loader.fmw workspace. Are there any warnings? How many features were written? If there are 0 features written, Review Part 2 .
• Check the SQL Server SINGLE_EDITS table. After a successful job, it should have new rows with the edits from the loader workspace. If it does not have edits: 
  • Review Part 1 and Part 5 for setting up the database. Check user permissions for all database objects.
  • Review Part 2 for configuring the loader workspace. Check the SQL Server writer’s database connection, table name, parameters and feature types. 
• If the database table has the edit rows, but you’re still not seeing updates in AGOL: 
  • Check the Automation Log File. Are there errors?
  • If there are triggered jobs listed, check their job logs. Are there any errors or warnings?
  • If there are no triggered jobs, check that your Webhook URL in the Create_Trigger.sql script matches the Webhook Trigger in the Automation. 
• If the Automation completes successfully, but there are still no updates in your AGOL feature layer: 
  • Review Part 4 for setting up the sqlserver_single_logger.fmw workspace. Check that the AGOL writer parameters are configured as they are in the examples

Data Attribution

The data used here (Streets.shp, previously BikeRoutesH.shp) originates from data made available by the City of Austin, Texas open data portal. It contains information available to the public domain.