Checking for the Existence of a File

The File System Task in SSIS doesn’t support checking to see if a file exists. You can work around this easily with a script task. Create a new script task and add a Imports statement referencing the System.IO namespace to the top of the script.

Imports System.IO

Then add the following to the Main method:

If File.Exists(Dts.Connections(“ConnMgrA”).AcquireConnection(Nothing).ToString()) Then
Dts.TaskResult = Dts.Results.Success
Dts.TaskResult = Dts.Results.Failure
End If

This script checks the file referenced by the ConnMgrA connection manager. If it exists, the script task returns Success, meaning execution will follow the Success constraint from the Script Task. If the file does not exist, the task will fail, and the Error constraint will be used. You could also set a variable with the results, and use that in an expression on a precedence constraint.

If, instead of using a connection manager, you want to get the file name from a variable, you can replace the If statement with the following:

If File.Exists(ReadVariable(“FileNameVariable”).ToString()) Then

The variable locking is occurring in the ReadVariable method. To see the definition for it, please refer to Daniel Read’s blog post here. This is a good practice to follow when working with variables in Script Tasks.

There is a Connect posting here requesting that the File System Task be enhanced to support checking for a file’s existence. If you’d like to see this in a future version, please vote for issue.

Leave a Reply