SSIS Success on Failure or removing Failure Option

Sometimes in SSIS we want a particular task to succeed but still have two paths or options, almost like a SSIS Success on Failure outcome.

For example, we may want a package to check to see if a file exists before running a data flow import task. We also want a “success” outcome even if the file does not exist, as this is not an true error. If you have a task running in SQL Server Agent every few minutes you do not want an error raised every time there is no file present.

The basis of this method is to use SSIS’ Expression and Constraint connection. This uses the “success” outcome of the task together with an evaluated expression.

To demonstrate our SSIS Success on Failure, lets create two variables called FileExists and ImportFile. One is a Boolean which we’ll use to indicate whether or not the file exists and the other is just the location of the file we’re going to import.

In our Script Task, called “Does File Exist?”, we’ll have some code that simply updates the variable FileExists to True if the file is present, in this case “C:\DataFolder\test.txt” ;

If (System.IO.File.Exists(CStr(Dts.Variables("ImportFile").Value))) Then
  Dts.Variables("FileExists").Value = True
  Dts.TaskResult = ScriptResults.Success
Else
  Dts.Variables("FileExists").Value = False
  Dts.TaskResult = ScriptResults.Success
End If

Then to connect the Script Task and the Data Flow Task we’ll choose Expression and Constraint. We can see this indicated by the fx (function) superimposed on the “success” connection below;

Then we’ll for our connection to proceed to the data flow task we’ll use the constraint of FileExists is True.

If the file does not exist the package just completes successfully at this point, assuming no previous errors. So although we succeed at both paths we have something similar to a SSIS Success on Failure outcome.

Thanks for Reading!