This post is inspired by a question asked in my blog this morning, about handling file-not-found errors from an SSIS package. A typical use case is as follows. An SSIS package is scheduled to run at every X minutes which processes files coming into a folder. However, a file may not be present every time the package runs. If a file is available it should be processed. If the file is not found, the package should exit gracefully, without generating an error.

There may be different ways of handling this. A few that I am aware of are

1. Creating a script task to check the existence of the file before proceeding to the actual import
2. Creating a for-each-loop that iterates over the files present in the folder
3. Using a 3rd party SSIS component that performs a file check

This post tries to demonstrate the option #1 listed above: handling the file-not-found case using a script task.

Checking the existence of the file using a script task

To start with, let us create two variables; one for the file name and another a boolean flag that indicates whether the file exists or not. The script task will turn this variable into true or false depending upon the existence of the file. Execution will continue to the next step only if the file exists.

Next, let us add a script task and data flow task into the package designer.

Now, let us add a constraint that ensures that the data flow task is executed only if the value of the variable Exists is set to true. Keep in mind that we will change the value of this variable from the script task depending upon the presence of absence of the file.

Let us now proceed with writing the script. Before we actually move into the script designer, open the properties of the script task and set the read-only and read-write variables.

Next, click on the button edit script which will open the script editor.

To start with we will need the following using statement.

using System.IO;

Next, add the following code into the main() function.

string file = Dts.Variables["User::FileName"].Value.ToString();
if (File.Exists(file))
{
    Dts.Variables["User::Exists"].Value = true;
}
else
{
    Dts.Variables["User::Exists"].Value = false;
}
Dts.TaskResult = (int)ScriptResults.Success;

Well, we are now ready for testing. Run the code in the presence and absence of the input file and ensure that it works.