I have developed a SSIS package which export data from database into flat files.
I would like to rollback whole SSIS package in case of any error. Of course, I've placed value in "TransactionOption" as "Required" at package level along with MSDTC service running.
1. I've used custom logging and sequence container.
2. In sequence container, I've placed log for each table and data flow for flat file
3. But, if error occurs after export in flat files i.e. export in flat files completed then;
4. Whole package should be rollback i.e. flat files should have zero records as well.
Currently, what is happening, in case of error, custom logging is not logging at database level but export in flat files are completed.
So, I would like to have that if error does occur then flat files should not have any row so that when we import data at destination end from flat files then data should be consistent at source and destination end.
Do you have any idea on it?