Execute SSIS Package based on File Existence

Often we have a requirement to check the file existence before we execute our package and it’s a best practice as well. So let’s understand how to achieve this in SSIS.

Quick Answer : The File.Exists () method will check the file existence on the specified path. The File.Exists() method will be available once you include System.IO; namespace to your script editor.

Complete Demo.

1. Open the SSIS Package/Create a new SSIS Package
2. Go to Control Flow
3. Create three new variables with the Package Level Scope. – See the image 1 below
4. Create one file (Excel or Flat File) as per your requirement and store it on a C:\
5. Variables declaration
   1. FileExists – type is Boolean
   2. FileName – type your file name in a value column.
   3. FilePath – type your file path in a value column
6. Go to  Control Flow and drag the Script Task to the container.
7. Double click on the  Script Task and go to Script Tab and assign the ReadOnly and ReadWriteVariables as shown in the below Image 2
8. Click Edit Script
9. Now, you will be in the script Editor.
10. Declare the namespace as  using System.IO;
11. Go to Main() method and copy the below code.
12. File.Exists will check the file existence for the specified path

public void Main()
{
  string fileNameWithPath=string.Empty ; 

//prepare the complete file path with name

fileNameWithPath =Dts.Variables["FilePath"].Value.ToString ()  + Dts.Variables["FileName"].Value.ToString (); 

            //check for the file existence
            if (File.Exists(fileNameWithPath))
                Dts.Variables["FileExists"].Value = true; 

            //display the result
            MessageBox.Show("File Exists " + Dts.Variables["FileExists"].Value.ToString()); 

            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success; 

        }

Now you are ready with the code. The above code will return true or false based on the file existence and you can use this value in the later part of the package to make conditional execution of the SSIS Package.

Conditional Execution of the package using Precedence Constraints

1. First connect your Script Task with the other Task Container i.e. Data Flow Task or any other task which you want to execute based on the file existence.
2. Now, double click on the green line (pipe line) which connects script task to another task.
3. This will open a Precedence Constraint Editor .
4. Under the Constraint Options group Select  --> Evaluation Operation as Expression as show in Image 3
5. In the Expression text box type the expression you want. In our case it' is :  @FileExists=true. This means if the variable is true the the next task will execute.
6. See the Image 3 for more clarity

Images

Image 1: Variables Declaration

Image 2 – Script Task Editor

Image 3 – conditional Execution using SSIS Precedence Constraints.

Happy Learning !!!