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:
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.
@[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.