Check if file exists: using SSIS

Hi All,
Many times, we have to do this simple checking “if file exists/available .. then proceed” in SSIS operations. And Microsoft did not put any facilities to achieve this task in SSIS with default components.

People also have raised this concern to Microsoft, but Microsoft explains that they have more priority works to deal with so this feature is not coming soon. you can check complete story here.

Follow these steps:
1. Create a Boolean variable (I call it a Flag_Variable). Assign default value to “False“.

2. Use “Script Task” to check if file exists in given location. And Set Boolean variable’s value for later use.
(I have used C# for demo.)

Add this namespace:
using System.IO;

Write this code to your Main() method:
System.IO.FileInfo myFile = new FileInfo(@”C:\InputDataFile.csv”);
Dts.Variables[“User::Flag_Variable”].Value = myFile.Exists;

Save and close the script window.

Note: you can parameterize the InputDataFile in script task like following.
System.IO.FileInfo myFile = new FileInfo(Dts.Variables[“User::InputDataFilePath”].Value.ToString());

3. Use “Precedence Constraint Editor” and choose “Expression and Constraint” value for Evaluation operation. Provide Use following expression and test it.

Expression: 
@[User::Flag_Variable] == True

Here, “Flag_Variable” is your Boolean variable.

Note: If “Flag” variable is of different type, e.g. string then expression expects “True”. So it varies based on the type of value you are comparing.

Choose “Logical AND. All constraints must evaluate to True” option and click OK.

That’s it. now you can connect to any controls task you want to proceed with on the success/True (File Exists) value. And finally, it should look like this 🙂

I hope this will help you somewhere.
Thanks, Khilit

5 (100%) 1 vote

9 Comments

  1. I’m sure this really the most important knowledge personally. At this point relieved looking at your personal write-up. Nevertheless would you like to comments about many typical facts, The web site personal taste is ideal, the particular reports is within simple fact excellent. Beneficial approach, Kind regards!

  2. I need to verify if a file exists and following the above procedure and having issues running the package

    Variable FileExists is boolean set to False created at Package level
    and have code similar to one above in Main() and constraint is set to “@[User::FileExists]==True”

    ****Script code
    System.IO.FileInfo myFile = new FileInfo(Dts.Variables[“User::SAPFileLocation”].Value.ToString());
    Dts.Variables[“User::FileExists”].Value = myFile.Exists;
    Dts.TaskResult = (int)ScriptResults.Success;
    **** ScriptCode

    I see the following error messages in package doesn’t execute.

    Error: The wrapper was unable to set the value of the variable specified in the ExecutionValueVariable property.

    Error: The type of the value being assigned to variable “User::FileExists” differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

    Any thoughts what i need to do ?

    • Hi Sai,
      Yes, The method seems to be correct. What i can see is that, you are directly assigning the value of “myFile.Exists” to the SSIS variable and thats what could be causing an issue. What version of SSIS (BIDS) are you using. I have created this demo in SQL 2012. i will check this today when i reach home.

      meanwhile, you can try this solution:

      ****Script code
      // Set Your File’s Path Here
      string sourceFilePath = Dts.Variables[“PATH_SRC_FILE”].Value.ToString();

      // Check if your file(s) is available
      string[] filesFound = Directory.GetFiles(sourceFilePath, fileFormat, SearchOption.TopDirectoryOnly);

      // If Found then set the value, True
      if (filesFound.Length > 0)
      {
      Dts.Variables[“User::FileExists”].Value = true;
      }

      Dts.TaskResult = (int)ScriptResults.Success;
      ****Script code

      Please let me know if you still face any issues on this.
      Thanks, Khilit

  3. I am having file declared as variable having mask Pay_MMDDYYYY.But my code is picking only that file which i gave in variable.It doesn’t pick file having any other datetimestamp other than this.how to pick the files having different mask.need to write it in C#

Leave a Reply