Problem: Reading XLS or XLSX in SSIS only works fine when the excel file is opened during the time of the extraction.
Solution:Use ACE driver instead of standard Jet.OLEDB driver. For example using an expression like this:
“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=”+ @[User::Filename_complete]+
“;Extended Properties=\”Excel 8.0;HDR=Yes;IMEX=1\”;”