is it possible to control the commit of a transaction at a job level? I mean, if something in a job goes wrong while running, usually one needs to rollback to the initial situation. I do no see any way to do this with talend...
In Talend Open Studio 1.0.0, a job can't be "rollbacked". The idea is pleasant but it seems very complicated to implement. The next improvement regarding transaction is simply to make them "configurable" in tDBOutput. See [Feature] 210.
I agree with you that the rollback is difficult to implement, and it depends on which database you are working on (for example in MySQL, if you do not use innoDB tables, you cannot manage transaction rollback). But, it is my opinion that it is necessary, if you want Talend to be used for professional applications. Without transaction control, for example rollback in case of error, you would have no possibility to guarantee data integrity. Since ETL tools are used mainly for DataWarehousing, and usually in DataWarehousing you have to manage a huge quantity of data, it is customary to load and transform data in an incremental way, for example on a time basis. When you are loading data on a staging area, there is no such a big problem, because you can delete data before loading it, and in this way you can always "come back". But, if you are working on business entities, like a fact table or a dimension, and you have to update rows, in case of error the only way to come back is to "rollback" the entire transaction which gave an error. The only thing you need in order to manage a rollback is to open only one DB connection at the beginning of the job, by setting autocommit to false where possible, and usa always this connection for every operation. Then you should capture SQL exceptions or oher exceptions and handle them by making a rollback. A commit governing at only operator level is not enough, since a job must be viewed as an Unit of elaboration, not an operator...