How to run SQL executor after translation has failed?

One way to do this is via a Topic. It is possible to assign a Topic to post on Failure when publishing a workspace:

Then create an automation triggered by that topic.

nielsgerrits wrote:

One way to do this is via a Topic. It is possible to assign a Topic to post on Failure when publishing a workspace:

Then create an automation triggered by that topic.

Thanks a lot, I will check this option. I have never worked with topics yet and didn't know about this.

If you did it in an automation would the trigger not just be the failed port for your sql process? the trigger to start the whole process could be a webhook in the application that kicks it off?

marias wrote:

nielsgerrits wrote:

One way to do this is via a Topic. It is possible to assign a Topic to post on Failure when publishing a workspace:

Then create an automation triggered by that topic.

Thanks a lot, I will check this option. I have never worked with topics yet and didn't know about this.

Automations is the successor of Topics (and notifications and subscriptions). Or it is the same, but with a new user interface? Not sure.

In Server 2021 it is positioned under Automations.

The downside of this technique is that (as far as I know) need to republish workspaces to activate the topic. So if you have a lot of workspaces, this takes some clicking.

I think yes. I must admit my experience with Automations is limited. Most Server stuff I do is triggered via our WebGIS (job submit via REST api), nightly schedules and external webhooks from Portal for ArcGIS.

nielsgerrits wrote:

One way to do this is via a Topic. It is possible to assign a Topic to post on Failure when publishing a workspace:

Then create an automation triggered by that topic.

Do you think with this approach it is possible to pass particular attributes from failed translation to the SQL executor or web application at least? ID which can be used to identify which exactly record in the DB should be updated. 

Not sure if I truly understand your issue, but I think what you need to do is create an Automation with a Manual Trigger.

The Manual Trigger is joined to your main workspace. The Failed port of you main workspace is joined with the workspace that updates the database.

Once that is set and running, you create an Automation App.

Check this link for more info: https://docs.safe.com/fme/html/FME-Flow/WebUI/Automation-Apps.htm

EDIT: I’m presuming that your web application is an FME Flow application, but now I see I’m the only one thinking that 😅 Ignore my answer If you are using a custom develop web application to run your workspace

I would skip using old-style notifications and go straight to using an Automation with a webhook trigger, if possible. Webhooks should be very simple to interact with for the developers of the webapp.

Documentation: https://docs.safe.com/fme/html/FME-Flow/WebUI/Automations-Triggers/Trigger-Webhook.htm

Do you mean you would replace a post request from an external application to submit a job via the REST api with an automation?

you woulld set up your automation similar to this: 

for the trigger set it to a webhook. copy and past the weebhook into your other applications requests, that will then kick of this automation. (the first workbench is your main process, and the second is where it fails and you want to run a sql statement.

Thank you all for replies! Let me just clarify my question.

We have external web application from developers that triggers a main workbench. Not Automation of FME Flow or FME App. 

Somehow web application needs to understand if FME translation has failed and receive a parameter from this translation, unique ID. It is not JobID on FME Flow, it is parameter from the workbench itself. 

Then we need update record in the DB that translation with particular ID has fails. 
I thought it can be SQLExecutor, but I don't know how to pass ID from failed translation to the SQLEcecutor.

it sound like you are expecting the workbench to fail in some instances? cant you just utilize a failed port or preform a test to isolate the “unique ID” of the failed record, to then pass into the sql executor as forming you sql statment e.g. Where unuique_id = @value(unique_id)? alternatively put an automation writer to get the relevant information from your workbench, so it can but utilized in a down stream workspace in a published parameter.

Unfortunately, I can't anticipate all cases when it may fail. I have covered many cases, but maybe there is something else. 

all i can think of is some work around along the lines of the below, but there is probably a better way: