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