Allright, I tried it out myself. I have an Excel sheet with a space in the name.

I can select the correct sheet from the dropdown, but I can't do a preview. However, the package ran succesfully.

Then I created a SQL statement with the Query Builder. I could select the sheet just fine.

However, preview didn't work, column information wasn't added. Switching back to the dropdown didn't work.

The "connection to the database" was lost. How the JET provider managed to do that, I don't know. Restarting BIDS solved the issue.

After the restart, I could create the SQL just fine and everything worked just as it should be.

Except that the Excel file was locked by BIDS itself. Restart BIDS again. Then it worked flawlessly.

So a space in the sheetname shouldn't be able to stop you. I did notice however that apparently you need to add quotes in the sheet name:

SELECT *

FROM ['Test data$']

Try the SQL statements with the quotes added and restart BIDS, it can do wonders 🙂