How to use transaction in apache camel route which involves SFTP and database

Question

I am trying to achieve the following in the apache camel route

1. Read data from database
2. generate files from the data
3. send the generated files to SFTP endpoint
4. save the generated file to the database.

I want to make transacted for step 3 and 4 so that files which are sent are always stored in the database and vice versa. any suggestions how achieve that?

I have tried using the transacted in the route files which are sent to SFTP endpoint and not reverted even though if it fails in step 4.