Rollback whole package including export in flat files


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
Who is Participating?
Alpesh PatelAssistant ConsultantCommented:
In Error event flush the File.
MohitPanditAuthor Commented:
Can you please give me detail information?
MohitPanditAuthor Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Anthony PerkinsCommented:
>>But can we remove only records from those flat files instead of delete all files?<<
No, you will have to re-write the entire file without those lines.
MohitPanditAuthor Commented:
Ok, thanks
MohitPanditAuthor Commented:
Thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.