SSIS Data Task Failure Not Executing Error Control Item

Another simple solution that took me about 1 hour to figure out.

I have a simple SSIS package that imports a flat file to a database table, performs a stored procedure on the imported data and moves the processed file into an archive folder. If the import failed, I wanted the file to be moved to an error folder for later manual processing.

The problem is that the failure branch was never executed and the failed files were never moved, stopping the entire control flow on the first bad file.

Control Flow:

SSISPackageControlFlowPreFix

Data Flow:

SSISPackageDataFlow

Seems very straight-forward. The failure constraint was created through the interface.

FailureConstraintAND

Notice the Multiple constraints is set to Logical AND.  It seems logical (pun intended) that this setting would not be important since there was only one constraint. Not true.

It must be set to Logical OR.

MultipleConstraints

You will notice that your failure line is now dotted and all of them are affected.

SSISPackageControlFlow

Now, my failed import file is properly moved to the errors folder and the rest of the files are processed appropriately.

This entry was posted in SQL and tagged , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

13 Comments

  1. brad
    Posted August 30, 2011 at 1:50 pm | Permalink

    Simple error but very helpful advice to those new with SSIS

  2. Posted August 30, 2011 at 6:34 pm | Permalink

    Thanks Brad. I am glad you found it useful.

  3. Amy
    Posted February 1, 2013 at 11:31 am | Permalink

    Very helpful. Thanks!

  4. Posted February 1, 2013 at 11:31 am | Permalink

    You are most welcome. I try to document stuff that took me a while to figure out but shouldn’t have.

  5. Andrius
    Posted March 19, 2013 at 11:59 am | Permalink

    Thanks. Your post saved a lot of time for me

  6. Santosh
    Posted April 25, 2013 at 3:37 am | Permalink

    Really helpful, Thanks 🙂

  7. Mohamed Shajid
    Posted June 14, 2013 at 4:09 am | Permalink

    I have got xml files in a folder and I want to import these files to SQL database and move to archive folder. using xml source I achieved this but my issue is some of the files (different structure) not getting inserted but moved to archive folder. I would like to move these files to rejected folder. Any help on this will be highly appreciated

  8. Posted June 14, 2013 at 8:23 am | Permalink

    It has been a long time since I have looked at this but my guess would be that your insert task is not throwing a failed when it goes to insert the records. Therefore it is taking the success branch and putting the file into the archive folder. Sorry that I cannot be more help.

  9. Madhu
    Posted July 25, 2013 at 4:39 pm | Permalink

    Very helpful since no one can imagine this to happen when running an SSIS package with failures setup. I tried for half a day to understand but finally your explanation gave me much needed info. Thanks!!

  10. Posted July 25, 2013 at 4:57 pm | Permalink

    Thanks for the feedback. It is very appreciated. Glad I can help you. It took me at least a day to figure this one out. That is why I wrote it up. I figured it should be documented somewhere.

  11. Posted November 20, 2013 at 9:32 am | Permalink

    Perfect thank you very much for the solution. worked like a treat.

  12. Posted November 20, 2013 at 9:57 am | Permalink

    I am glad it helped. Too me forever to figure it out the first time.

  13. Susan
    Posted March 24, 2014 at 5:56 pm | Permalink

    Thank you for posting this. I have been going crazy trying to figure out why my error path was not being followed.

    SSIS is not very intuitive. thanks for the help

Post a Comment

Your email is never published nor shared. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*
*