Nice! What is causing this? It turned out that the name of the sheet was 31 characters long. I removed the last character, saved the spread sheet and voila! the name of the sheet is in the drop down box. So it seems that while the sheet name limit in Excel is 31 characters when dealing with it in SSIS the name of the sheet cannot be longer than 30 characters because the 31 characters need to be able to include the $ sign which indicates sheet name and not a range name.
Update: this may not have been the problem. I have "fixed" the error by saving the Excel file without changing the name of the sheet. So I am back to drawing board with this error.
Now if I could just figure out the "Value does not fall within the expected range" error that I get when I am trying to view the columns on this same component.
Update: I resolved this last error by deleting my Excel Source component and recreating it.
