I am running a job in DI Studio job. I am using a permanent cross reference table(SAS dataset) to load an oracle dimension table. So if some error occurs while loading to the oracle table i want that the data that is loaded in cross reference table should be rolled back. Is that possible? I tried ERRLIMIT = 1. But this works only for rdbms tables or pc files. What is the option for sas datasets?
Can you DI Studio job create a backup-copy of the SAS dataset, then detect an Oracle table load error, and conditionally restore the SAS dataset on an error condition? I expect that you will find SAS automatic macro variable(s) set with a code depending in the success of your Oracle connection, as long as you can continue executing your job after the error occurs to clean-up (your application code) as needed.
You may find useful information at the SAS support http://support.sas.com/ website either using the SEARCH facility or with using the Google advanced search argument listed below: