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

You may experience the below error if you try to connect with latest version of Microsoft Excel using Integration Services;

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'

This error comes when you try to retrieve Excel Sheets from the connected Excel Workbook. In order to solve this, you need to install correct OLE DB Providers and Drivers for Microsoft Office files. Follow the link below for downloading the correct version;

https://msdn.microsoft.com/en-us/library/cc280527.aspx

Note that if you have 32-bit version of Office, then you need 32-bit drivers else you need 64-bit drivers.