I have an Excel 2016 workbook with some data that I want to set as my source within SSIS (using Visual Studio 2015 SSDT), however, I keep getting the following error:
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'
I read somewhere that changing the SSDT from 64bit to 32bit will address this issue, which it did, but only when setting an Excel workbook as my destination.
What am I missing please?
"I solved it by installing 2007 Office System Driver and Microsoft Access Database Engine 2010 Redistributable.Even I'm using 32 bit Office and 64 bit OS and its working well.The download links http://www.microsoft.com/en-in/download/details.aspx?id=13255 and http://www.microsoft.com/en-in/download/confirmation.aspx?id=23734"
Try using different versions of the Access Database Engine dependent on your source file
Source: Microsoft.ace.oledb.12.0 not registered on the local machine