In Visual Studio, below message appear when opening SSIS source from Excel sheet.


     Could not retrieve the table information for the connection manager 'Excel Connection Manager'.

     Failed to connect to the source using the connection manager 'Excel Connection Manager'

Visual Studio - SSIS

As a pre-requisite, a 32-bit Microsoft.ACE.OLEDB.12.0 (or 15.0 or 16.0) Provider needs to be installed on development 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


Download 32-bit Microsoft.ACE.OLEDB from below.

https://www.microsoft.com/en-au/download/confirmation.aspx?id=13255


SSMS

Select 32 bit when executing the SSIS package.  


Make sure setup the same in SQL Job, when creating a SSIS package as a job.



Source Reference : https://social.msdn.microsoft.com/Forums