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:
Data Flow:
Seems very straight-forward. The failure constraint was created through the interface.
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.
You will notice that your failure line is now dotted and all of them are affected.
Now, my failed import file is properly moved to the errors folder and the rest of the files are processed appropriately.
13 Comments
Simple error but very helpful advice to those new with SSIS
Thanks Brad. I am glad you found it useful.
Very helpful. Thanks!
You are most welcome. I try to document stuff that took me a while to figure out but shouldn’t have.
Thanks. Your post saved a lot of time for me
Really helpful, Thanks 🙂
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
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.
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!!
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.
Perfect thank you very much for the solution. worked like a treat.
I am glad it helped. Too me forever to figure it out the first time.
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