When working with databases, Etlworks supports three transaction-management styles: Auto Commit, Manual Commit, and Rollback on Exception.
Auto Commit
When creating a database connection, you can set the Auto Commit option. It is disabled by default — Etlworks controls when to commit or roll back the transaction.
When Auto Commit is disabled
- If the flow finishes successfully, Etlworks coordinates the commit across all open database connections with Auto Commit disabled. When the flow is part of a nested flow, the coordinated commit fires when the outer nested flow finishes.
- If the flow finishes with an error, Etlworks coordinates a rollback across all open database connections with Auto Commit disabled.
- If the flow finishes with an error but is configured to ignore all or specific exceptions, Etlworks commits anyway. You can override this behavior.
When Auto Commit is enabled
Each DML statement commits automatically. Enable Auto Commit on the database connection.
Manual Commit
SQL flows can commit the transaction regardless of the Auto Commit setting on the connection. If the SQL flow runs multiple ;-separated statements, the commit fires after the last statement.
Rollback on Exception
Etlworks always rolls back the connection on an exception — unless the flow or transformation is configured to ignore all or specific exceptions. Override this by unchecking Rollback on Exception on the On Exception tab.
Connections created per transformation or SQL flow
When ETL transformations are configured with Create new database connection for destination,
or when SQL flows are configured with Open new connection,
Etlworks creates a separate database connection for that transformation or SQL flow execution. These connections follow the same transaction rules as regular connections:
- They participate in coordinated commits and rollbacks based on the flow's outcome.
- If Auto Commit is disabled, Etlworks commits or rolls back the per-transformation / per-flow connections together with the others when the main flow completes.
- If Auto Commit is enabled, each DML inside the connection commits automatically.