• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

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
0
MohitPandit
Asked:
MohitPandit
  • 4
2 Solutions
 
Alpesh PatelAssistant ConsultantCommented:
In Error event flush the File.
0
 
MohitPanditAuthor Commented:
Can you please give me detail information?
0
 
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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.
0
 
MohitPanditAuthor Commented:
Ok, thanks
0
 
MohitPanditAuthor Commented:
Thank you
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now