I have SQL Server 2019 and running an SSIS package via SQL Server Agent on a schedule. The first step of the job runs the SSIS package to import an Excel file into a database table. If the file doesn't exist (occasionally after holidays, etc. there will be no report to import) the job of course files because no file exists but then the SQL Server Agent creates a blank excel workbook with the name of the import file. How do I stop this from happening? It does not happen in SQL Server 2012, in that version the job fails but no blank workbook is created.
I have attempted to set the execution options to "Fail the package on validation warnings" and "Validate package without executing" and in both cases the execution fails but the blank excel workbook is still created.
My recommendation is to use a SSIS Script task to identify if the file exists or not prior to running the dataflow. Use the link here on how to write one.
SSIS Script task to check if file exists in folder or not