Files
As of FME 2024.0, support for Esri ArcGIS Online (AGOL), ArcGIS Enterprise Portal, and ArcGIS Server Feature Service formats has transitioned to a unified approach using the new Esri ArcGIS Feature Service (Format). This format replaces the legacy reader/writer formats for each of the three services.
Starting in FME 2026.1, the legacy formats will be hidden in the Quick Add menu. Instead, equivalent functionality is now provided by the downloadable Esri ArcGIS Connector package, available on FME Hub. The package also includes new web services and key transformers such as the ArcGISOnlineConnector, ArcGISAttachmentConnector, and ArcGISBranchVersionManager. The package can be installed directly through the Quick Add menu in FME Workbench. Once installed, the new Esri Feature Service format will appear in the Gallery as a unified method for interacting with all three types of Esri ArcGIS Feature Services.
For details on this transition, including guidance on updating existing workspaces, please refer to the article: Working with Esri ArcGIS Feature Services in FME.
Introduction
FME Flow can respond to database events in real-time by using external database triggers. With FME Form and FME Flow, you can create a real-time data pipeline between your database and external applications.
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 into major sections, and the steps are best carried out in the order they are written. 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 for the example. The following steps use the Microsoft SQL Server Management Studio (SSMS) application to perform all tasks.
1. Create a New Database
Open your SQL Server. Create a new database, and 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 specifications. Ensure you replace all occurrences within the script (this would be a good job for the “Find and Replace” function!).
- Database name. The example database is named “fme-single-edits.” Keep this if you’re following the example.
- 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 an FME Flow App will run. 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 offers 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 specifications.
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 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. The edit will appear as a new row in the table in SQL Server. 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 typing 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 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 cannot access 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 created. Set Layer Definition to “Import from Dataset…”
If you are using FME 2025.1+ you need to download the Esri ArcGIS Connector from FME Hub to use as the writer
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, 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:
- Replace the webhook's URL (@sUrl) with the URL copied from your clipboard. Leave the ‘?’ at the end of the URL.
- 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 to your SQL Server Connection. If your connection is not listed, upload the connection along 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:
- 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.