It is not uncommon that you run into the requirement that you have to check whether a particular file exists before starting the ETL process that imports the data. There is no straightforward solution to implement this in SSIS except writing your own C# code using a Script component. We recognize the challenges faced by developers in working with such requirement, so we create this blog post to show you how to achieve this in SSIS without writing a single line of code by leveraging the Premium File Properties Task offered in the KingswaySoft SSIS Productivity Pack.
Premium File Properties Task is a control flow component that can be used to read file properties or update file properties. It supports various types of connections including Local File, SFTP, FTPS and cloud storage services like Amazon S3, Google Cloud Storage, etc. If you want to learn more about the component, you may click here for the detailed information of the component.
Checking file existence in SSIS can be extremely easy using the KingswaySoft Premium File Properties Task, all it requires is merely a few mouse clicks. Simply drag and drop the component into the package and open the component, specify the Connection Manager, Source Path and Read File Properties action, add “SourcePathExists” Boolean type property to the Property Output Mapping grid and provide a Boolean type variable to store the file existence check result.
The screenshot above shows an example of checking file existence at local file system path “D:\SampleFile.csv”. If the file exists at the specified file path, a True value will be assigned to the specified user variable, otherwise the False value will be assigned.
After configured the Premium File Properties Task component, next, we can use expression in SSIS Precedence Constraint to only execute the next task once the expression is evaluated to True.
Right click on precedence link between two control flow tasks to open the context menu, choose Edit to open the precedence constraints editor:
The expression “@[User::BooleanVariable]==true” is used in SSIS Precedence Constraint Expression, which checks if the file path existence result value stored in @[User::BooleanVariable] variable is equal to True.
The overall Control flow design of the package is shown in the screenshot below, as you may notice, there's a small fx icon next to the constraint, this indicates that the constraint is evaluating expressions.
Let’s execute our package to test the package design, the following screenshot is taken when the file doesn’t exist:
You can see that only the Premium File Properties Task runs, but the Process File data flow task does not.
The below screenshot confirms that the Process File data flow is executed when the specified file path exists.
We hope you find this article helpful. Please feel free to let us know if you have any further comments or suggestions.