0

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.

enter image description here

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?

BdR
  • 2,770
  • 2
  • 17
  • 36
  • 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 Aug 01 '19 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 Aug 01 '19 at 13:48
  • Like I said, it's counter intuitive. But you sound like you've got it under control. – Jacob H Aug 01 '19 at 13:49

1 Answers1

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.

S Mishra
  • 26
  • 4
  • 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: https://www.mssqltips.com/sqlservertip/3575/continue-a-foreach-loop-after-an-error-in-a-sql-server-integration-services-package/ – BdR Aug 01 '19 at 15:29