I was struggling to use excel as a source in my data flow. After specifying the file name and path in the connection, i kept getting "External table not in specified format". Tried everything like deleting all sheets except the one which had data, renaming the sheet with a dollar symbol in the end.
Finally i tried changing the data access mode to sql command and after several experiment found the syntax to query excel sheet.
SELECT * FROM [SheetName$]
But this too worked only for a while before the excel source transformation started showing the same errors.
The problem i found in my case was with the actual source, which though had .xls extension was created in office 2007. So when i opened the file and again saved it in excel for 2003 things started to perform as expected.
I was able to select the source from all access methods.
