Question

FME workbench and transactionality when FAILED

  • 30 August 2018
  • 1 reply
  • 6 views
giosp
Rank
Badge

I'm testing a workflow that permit to rollback all the changes made in a workbench execution. There is a possibility to rollback some operations from start to end including the truncate operation?

I have a workflow that read data from a source and write in another one. Writers are set to truncate. If the write process has an error and the workbanch has a failure the truncate process is commited. I'm not able to rollback before the truncate step. Anyone has an idea?

A possibility is to have a staging area and if the workbench is completed without problems a second workflow start with the real update from staging area to the work area.

1 reply

david_r
Rank
Userlevel 4

I agree fully about using a staging area, I would consider that the best-practice solution.

When data has been fully loaded into the staging area it's usually very simple to copy the data into the production schema using a SQLExecutor and something like

begin
  delete from prod.my_table;
  insert into prod.my_table select * from staging.my_table;
  commit;
end;

Not only would that be a single transaction, but it would also be much faster than doing the transfer with FME since no data leaves the database, which means reduced down-time on the production datasets.

Reply


Community Stats

28,669
Posts
109,359
Replies
36,531
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.