Rollback whole package including export in flat files
Hello,
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.
For e.g.,
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?
Best Regards,
Mohit Sharma
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.
For e.g.,
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?
Best Regards,
Mohit Sharma
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Alpesh,
I got it. Please find below steps which I did:
1. I took "File System Task" on package "On Error" event.
2. After that, once double click on this component, I selected "Delete Directory Content" and passed the SouceConnection i.e. folder name.
3. Now, I can see that when error comes then all files do delete from that location.
Thanks for this.
But can we remove only records from those flat files instead of delete all files?
Best Regards,
Mohit Sharma
I got it. Please find below steps which I did:
1. I took "File System Task" on package "On Error" event.
2. After that, once double click on this component, I selected "Delete Directory Content" and passed the SouceConnection i.e. folder name.
3. Now, I can see that when error comes then all files do delete from that location.
Thanks for this.
But can we remove only records from those flat files instead of delete all files?
Best Regards,
Mohit Sharma
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, thanks
ASKER
Thank you
ASKER