I already answered a similar question here:
As a pre-requisite, a 32-bit Microsoft.ACE.OLEDB.12.0 (or 15.0 or 16.0) Provider needs to be installed on dev. machine with Visual Studio (VS).
You need to do 2 things in Visual Studio:
- Set the SSIS project in 32-bit mode via Run64BitRuntime to false.
- In Excel Source Adapter, specify SQL statement instead of referring to Excel's sheet name only:
- SELECT * FROM [SheetName$A1:B3], by specifying a certain range
- SELECT * FROM [SheetName$], whole sheet
P.S. It seems that it started to happen with the latest ACE 12.0 update v.14.0.7015.1000