I have a server that can use the Import Wizard to create and execute an Excel Import task. Using a visual studio 2010 integration services package that I am creating on that server, I am attempting to automate that procedure.
When attempting to design the packaging, the issue arises. I added a connection for Excel and linked it to the local disk's Excel file (the same file I have already successfully imported using the import wizard). When I add an Excel Source to the DataFlow and specify the Excel connection, I only get "No tables or views can be loaded" and receive the following error when I go to the Name Of the Excel Sheet Drop Down.
"Could not retrieve the table information for the connection manager. Failed to connect to the source using the connection manager ..."
I don't know why it is failing, and I can't locate this issue recorded elsewhere. The file is not in use, and the directory is shared with authenticated users.
Any suggestions on how to fix this error? Does running this in 64-bit mode have problems, which I can understand, also apply to development?
It is an Excel 2007 file, I should note.
XLSX and Excel 2007 are selected as the connection.
2019-11-08 If someone tries the answer provided by GavB841 below and it works, please let me know. (I'm no longer employed in this field.)
