Prevent package from failing when there is no file for File System Task to move

Question

Post reply

Prevent package from failing when there is no file for File System Task to move

laura.smith

Mr or Mrs. 500

Points: 564
More actions
April 3, 2019 at 8:14 am

#2650528

Hello,
I have a SSIS package that has a File System Task inside a Foreach Loop Container that's used to move files. The files are generated every 5 minutes, and the package is run by SQL job agent every 5 minutes but last night they got out of sync and the package failed, because there was no file to pick up, the error message I got was 'Could not find file [filename]'. Is there anything I can do so that if this happens again the package won't fail and the job will move on to the next step? I have got DelayValidation set to true if that makes a difference, and I'm using SQL Server 2014 and Visual Studio 2015.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

@Taps SSC Eights! Points: 879 More actions · Answer 1

You can add a script task to check if file exists - searching this on internet will give a number of options

handkot SSCertifiable Points: 5249 More actions · Answer 2

https://www.mssqltips.com/sqlservertip/3575/continue-a-foreach-loop-after-an-error-in-a-sql-server-integration-services-package/

This reply was modified 5 years, 7 months ago by handkot.

I Have Nine Lives You Have One Only
THINK!

Martin Schoombee SSCoach Points: 19126 More actions · Answer 3

They way I usually like to solve this is to create an empty file (call it a template) for each source file. I copy the empty files to the processing folder and then overwrite them with the incoming source files.

This works well if you have multiple source files and want to continue processing (or make that decision later in the process) even if a source file is missing.

david_h_edmonds Default port Points: 1430 More actions · Answer 4

Hi, this works for me when I want to be selective with files.

https://stackoverflow.com/questions/6598139/how-to-loop-only-through-files-that-dont-exist-in-destination-using-an-ssis-pac

Dave

laura.smith Mr or Mrs. 500 Points: 564 More actions · Answer 5

Thanks for all your suggestions. I have gone with adding an event handler to the System File Task container and setting propagate errors to false.

david_h_edmonds Default port Points: 1430 More actions · Answer 6

Hi, just be careful that you aren’t missing different errors on files that are actually present. Do you have some sort of audit table/ process that logs the outcome of each file?

Dave