i have requirement to roll back the data if any of the steps is a failure in the package.Below are the steps in the package.
1. Load the records from file to target table ( interface)
2. validate the data (based on the values in the columns)
3. If any of the validation fails put the failed records in to a file (ODI SQL UNLOAD)
4.Delete the failed records (procedure)
Now in the process since the commit option is set to false in the interface. sql unload is not able to fetch the data from the table which is uncommitted.
Is there a way to set the transaction of the tool ?
Thanks for your response.
Yes i can create a procedure to rollback. But i would like to make use of transactional control in ODI.
i would like to know a way of using this odi sqlunload in the same transaction as my interface is executed so that it can fetch uncommited data.This would reduce the additional procedure to roll back.
What about the use of Conditions and Reference Keys in your interface. If your validation rules are not complicated you may try.
Please, check this example.
You will have your error in a Error Table.