SSIS 2005 - How to check a file to see if a files does not exist

Question

How do you check to see if a file does not exist in SQL Server Integration Services 2005?

Is there a native SSIS component which will just do this for you?

Answer 1

I have checked for file existence this using the Script Task and then branch accordingly.

You can do something like

If System.IO.File.Exists("\\Server\Share\Folder\File.Ext") Then
    Dts.TaskResult = Dts.Results.Success
Else
    Dts.TaskResult = Dts.Results.Failure
End If

Although there are no native components for this, there are several third party components for SSIS that you can use for this purpose.

The File System Task in SSIS is basically for move, copy, delete, etc., but does not support file existence checks.

Answer 2

@Raj More gave a good solution. Another way that I have used before is to create a Foreach Loop Container that loops over the file system for a file spec. If you know the name of the file you want, then you can set the name in a variable and set the spec to equal the variable in the expression tab for the Foreach Loop Container. You could also just specify or a directory or a partial file name if you don't know the exact name but know the naming convention or know there will be no other files in the folder.

If you want to take a specific action based on whether or not there is a file, then you could create a variable with a default value of 0 and create a script task in the Foreach Loop Container that increments the variable. You could also just put the commands in the Foreach Loop Container that you want to execute if you want to execute it for the existence of each individual file. If you want to take an action based on the absence of the file, then you could restrict your precedence constraint after the Foreach Loop Container so that it is restricted on constraint and expression and make it check if the counter variable is > 0.

@Raj's solution could also be used to increment the variable. Instead of using an If Else to raise an error or success result, you could do this:

C#

if (System.IO.File.Exists("\\Server\Share\Folder\File.Ext"))
{    Dts.Variables["my_case_sensitive_variable_name"].Value = Dts.Variables["my_case_sensitive_variable_name"].Value + 1;
}

VB.NET

If System.IO.File.Exists("\\Server\Share\Folder\File.Ext") Then 
    Dts.Variables["my_case_sensitive_variable_name"].Value = Dts.Variables["my_case_sensitive_variable_name"].Value + 1
End If 

The advantage of this approach is that the package may not need to fail in the absence of a file. You could also use a variable name if the file changes that you could define either as a variable in the package or just solely created in the script task. The only short-coming of @Raj's approach is that you have to know the file name you want to check.

Another possibility is to execute a File System Task to rename the file to its existing name or copy the file to its existing location. If the file doesn't exist, then you can route the error to an action. I don't recommend this solution, but I remember using it years ago in one instance where it actually made sense. But in that particular instance, I was actually copying it to a real location.

Good luck!