SSIS-when the input file is not found

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.

1.using System.IO;

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

01.string file = Dts.Variables["User::FileName"].Value.ToString();

02.if (File.Exists(file))

03.{

04.Dts.Variables["User::Exists"].Value = true;

05.}

06.else

07.{

08.Dts.Variables["User::Exists"].Value = false;

09.}

10.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.