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?

The red arrow is the flow for errored steps. Make another "Move CSV file" step for your Error folder, and connect it to your "Handle next CSV file" to it. Or you can handle the file moving logic BEFORE processing, rather than after. It seems counter intuitive, but this way you can separate the file moving from the processing. — Jacob H
– Jacob H, Commented Aug 1, 2019 at 13:14
Thanks for the comment, but that is not what I asked. I would use the red Error Output arrow if it was there, but the Control Flow item "Handle next CSV file" does not have one. And moving the file before processing it defeats the purpose of having separate Done and Error folders. — BdR
– BdR, Commented Aug 1, 2019 at 13:48
Like I said, it's counter intuitive. But you sound like you've got it under control. — Jacob H
– Jacob H, Commented Aug 1, 2019 at 13:49

S Mishra · Accepted Answer · 2019-08-01 14:59:03Z

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.

answered Aug 1, 2019 at 14:59

S Mishra

264 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BdR Over a year ago

Thanks, points 1 is what I was looking for. I wasn't aware that you could change the arrows from green to red by changing the Value property to Failure. And I managed to continue the Foreach loop on any errors, using the Propagate Variable as described here: mssqltips.com/sqlservertip/3575/…

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related