Package Design which Checks existence of data file .Only If it exists Execute Package,If not found exit package

Using the same earlier package

http://simplyssis.blogspot.com/2015/08/how-to-use-begin-tran-in-ssis-rollback.html

Let's now add a requirement that the package checks for the existence of the data file.If the data file exists it then executes the tasks if not exits the task.

To check the existence of the data file we use the SCRIPT TASK and a user variable with the full name(name & location) of the data file.

The C# code to check for the existence of the file is:

if (System.IO.File.Exists(Dts.Variables["User::file_name"].Value.ToString()))
            { Dts.TaskResult = (int)ScriptResults.Success; }
            else
            { Dts.TaskResult = (int)ScriptResults.Failure; }

Then Add SCRIPT TASK with precedence constraint on Success to BEGIN TRAN SQl command.If the Script task doesn't find the data file it fails and the following tasks are not executed.

Also change the delayvalidation property of the transaction container  to true.If this is set to False the entire package will fail at pre-execute stage and even the script task won't execute.Delayvalidation property delays the validation to the execution stage

 

If the script task finds the data file the following tasks are executed