dadumas
06-24-2009, 04:31 PM
Challenge:
I need to be able to group a set of job transformations together as one logical transacton in the database. So, for example, in Job 1, transformation A, which performs a discrete set of database ETL, is followed by transformation B, which also performms a discrete set of database ETL. As a requirement, if transformation B fails, I also need to rollback all Database DML performed in transformation A. I have used the "use unique connections" in transformation settings, but across multiple transformations, they act as separate transactions.
Question:
Is there a way to create a "transformation container" in a job, that groups Transformation A and Transformation B, such that, they succeed and fail as a whole, and commit and rollback is controlled at this "container" level?
I need to be able to group a set of job transformations together as one logical transacton in the database. So, for example, in Job 1, transformation A, which performs a discrete set of database ETL, is followed by transformation B, which also performms a discrete set of database ETL. As a requirement, if transformation B fails, I also need to rollback all Database DML performed in transformation A. I have used the "use unique connections" in transformation settings, but across multiple transformations, they act as separate transactions.
Question:
Is there a way to create a "transformation container" in a job, that groups Transformation A and Transformation B, such that, they succeed and fail as a whole, and commit and rollback is controlled at this "container" level?