How to prevent Excel import in SSIS package when there is no file to process?

2
votes

I have an SSIS package that imports Excel files. Inside the package, I have a Script task that checks whether the Excel file exists or not before executing the Excel import process.

I am unable to execute the package when the Excel file doesn't exist because of the AcquireConnection error.

How can I prevent the package from executing when there is no Excel file available for import?

1
exceldatetimessispackage
If you check if the file exists first, how can there be an error afterwards? - Jeroen Bolle
@Siva, that might be the case, good call! - Jeroen Bolle

1 Answers

4
votes

Perform the following steps to avoid the package from failing.

  • Set the ValidateExternalMetadata property on the Excel Source to False.
  • Set the DelayValidation property on the Excel Connection to True.

The package validation phase happens first even before your Script task can execute. If the Excel file, doesn't exist then the package validation phase will fail. Setting the above property values will delay the validation and you can check if the first exist or not in your script task. If the file doesn't exist, you can abort the package execution.