Skip to main content
Question

FME workbench and transactionality when FAILED

  • August 30, 2018
  • 1 reply
  • 17 views
giosp
Contributor
Forum|alt.badge.img+6
  • giospContributor
  • Contributor

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
Evangelist
  • david_rEvangelist
  • August 30, 2018

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


Most helpful members this week

Community Stats

30,858
Posts
116,996
Replies
38,689
Members
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.