Check if file exists in SSIS

There are times we may have to use other than database sources such as text file, csv or Excel file. It is a good practice to check if the file we are working one exists before using it in a data flow. In this blog post we will see some of the different ways we could check if file exists in SSIS.
The simplest case will be checking a file that has a fixed file name and extension.
Let us first create a Boolean variable where we could store the value after we check the file exists.

Now drag a script component to the control flow area

Add the variable we created to the ReadWriteVaiables list

Click on Edit Script… button and type the following script

// Add this library
using System.IO;

// Add this to main function
 
if (System.IO.Directory.GetFiles(@"D:\", "textFile.txt").Length > 0)
{
       Dts.Variables["User::isFileExists"].Value = true;
}
else
{
       Dts.Variables["User::isFileExists"].Value = false;
}

After you finish you code it should look like this

Now attach the next tasks like the one below

Now let us change the precedence constraints so that we could decide which tasks get to execute

Change the Evaluation operation to Expression then for Expression text box click on “…”

In the expression text box add our variable @[User::isFileExists]. Since our variable is a Boolean variable we don’t need to compare with anything but if you want you could type it as @[User::isFileExists] = true. Now do the same for file not exists add ! @[User::isFileExists]

Since the file exists when the package executes you could see that the “If file executes” gets to execute.

Okay this is good but most of the time we may have to check if a file that has a certain type exists such as if an Excel file exists in a certain folder. To do that let’s add two string variables folderName to hold the folder physical address and excelFileName this will hold what the excel file name after in the folder we are checking.

Now add the new variable folderName as ReadOnlyVariables to the Script Task Editor and excelFileName as a ReadWriteVariable.

Add this script to your main function.

if (System.IO.Directory.GetFiles(
      Dts.Variables["User::folderName"].Value.ToString(), "*.xlsx").Length > 0)
{                
      Dts.Variables["User::isFileExists"].Value = true;
      //~~>this will return the first excel file name
      Dts.Variables["User::excelFileName"].Value =
           System.IO.Directory.GetFiles(Dts.Variables["User::folderName"].Value.ToString(),
                "*.xlsx")[0].ToString();
}
else
{
       Dts.Variables["User::isFileExists"].Value = false;
}

This should be enough to check the file exists. But let us add another script task to check if we could actually return the name of the file if it exists. Then connect it with the current Script task like below.

Next add the following script to the main function of the new script task. Don’t forget to add the variable to a ReadOnlyVariables list.

MessageBox.Show(Dts.Variables["User::excelFileName"].Value.ToString());

Now that returns

Which is the same as the file I have in my D: