Rollback whole package including export in flat files

Posted on 2011-10-11
Last Modified: 2012-05-12

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
Question by:MohitPandit
    LVL 21

    Accepted Solution

    In Error event flush the File.
    LVL 5

    Author Comment

    Can you please give me detail information?
    LVL 5

    Author Comment

    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
    LVL 75

    Assisted Solution

    by:Anthony Perkins
    >>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.
    LVL 5

    Author Comment

    Ok, thanks
    LVL 5

    Author Closing Comment

    Thank you

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now