SSIS foreach loop, move each file to folder Done or Error based on any errors during processing

Question

I'm working on an Integration Services project, it will handle a few files every day, process them and either move them to a Done folder or an Error folder, depending on if there were errors while processing the file.

I haven't worked much with SSIS before, only on simple data flows, but I've managed to iterate through all files and process them using this answer. I've added an File System Task which moves the file to the Done folder, based on the DestinationFullPath variable. This all works, and when there aren't any errors, all files are processed and moved to the Done folder.

However, when there is an error in one file (like a missing column, error when storing in database, patientid not found etc.) the entire flow stops.

What I want, is after processing one file it should catch any errors and either move the file to a "Done" folder or to an "Error" folder, and then just continue with the next file.

Is something like this possible, if so how? I've tried changing the DestinationFullPath variable and set it to the Error folder, but then it still throws an error and stops the flow for other files. Also, the Data Flow Task "Handle next CSV file" (inside the Foreach Loop Container) doesn't have a red Error Output arrow, is that correct? Can I add it somehow?

Answer 1

There are few workarounds: 1. Create another Control flow task For ex: Move Error files and connect Green Precedent from Handle next CSV file to new component, right click on constraint and click on Failure(Red constraint) 2.Handle error within data flow task by utilizing on error property of data flow components, please make sure to keep Delay validation to True on Data flow component so that it doesn't fail on startup.