Hello,
My requirement is to truncate and load an Oracle table every day with a new file. But the catch here is, do not truncate if there is any issue with the file. My design has a external file, XML Input, transformer and Oracle Connector. I have mentioned to truncate the table. But when the job fails due to any parsing issues, the truncate statement is getting executed at the starting of the job and deleting the data. I can't have any intermediate files to first parse and validate then load. Is there any way I can stop truncating the table when the job fails?
Thanks, Yugee
Oracle Connecter - Do Not Truncate on Failure
Moderators: chulett, rschirm, roy
If you're saying that you cannot, for some reason, split the existing design into two jobs (which seems like the most logical thing to do...), then I suppose you could change the Oracle Connector target table to a temporary work table that still gets truncated no matter what. Then you could perhaps craft some sort of After-SQL statements, with annotations, to check for success (records in the temp table) and if it passes that test then truncate and load the real table. Personally I would just keep it simple and go with two jobs so that the logic is more obvious to the next developer who comes along in the future.
Choose a job you love, and you will never have to work a day in your life. - Confucius
I agree with your design, I copied the job and replaced OC with a CopyStage. I did all my field validations in this job. The next job will just take the file and load it to the actual table. There is still a chance of truncating the table if any issue with loading data to table, but I have covered 99% of the validations in previous job. If any such things occur while testing, I will add them as well.
Thanks a lot for your suggestions.
Thanks a lot for your suggestions.