When working with databases, the Etlworks Integrator provides 3 options for managing transactions:
When creating a database Connection, you can specify the
Auto Commit option. The
Auto Commit is disabled by default, therefore the system controls when to commit (or roll back) the transaction.
If Auto Commit is disabled
- The system will coordinate the commit across all opened database Connections with
Auto Commitdisabled if the Flow was executed successfully. If the Flow is a part of the nested Flow the coordinated commit will be executed when the main nested Flow is finished.
- The system will coordinate the rollback across all opened database Connections with
Auto Commitdisabled if the Flow was executed with an error.
- If the Flow was executed with an error but was configured to ignore all or specific exceptions, the system will commit the transaction. Read how to override this behavior.
If Auto Commit is enabled
Enable automatic commit on each DML statement by enabling the
Auto Commit option when creating a database Connection.
For SQL Flows, you can configure them to commit the transaction regardless of the
Auto Commit settings for the Connection.
If SQL Flow executes multiple
; separated SQL statements, the commit will be issued after the last statement.
Rollback On Exception
The system always rollbacks Connections on exception, unless the Flow or transformation was configured to ignore all or specific exceptions. You can override this behavior by unselecting the option
Rollback on Exception under the
On Exception tab.