Please surround any code or links you post with the appropriate IFCode formatting tags. The packages are executed via SQL Server Agent jobs nightly.
Does anyone have any idea why opening the Excel file before running the job in SSIS thru Visual Studio would allow the errors below to be avoided and the job to pass?
If that fails with the same error, you can be pretty sure that you have a permissions problem of some kind.
Running things from VS is very different from running things within a SQL Agent job, primarily due to the fact that Agent jobs run under the execution context of the SQL Agent service's service account.
The failure occurs at the Data Import control flow which is supposed to read from an Excel file and import that data for further manipulation down the chain before inserting/updating a database table.
What is weird is this: if we first open the Excel file and load it in Excel from the share location, and then run the SSIS job, it passes!