April 19, 2011 at 2:49 pm
File is an .xls (Excel 97 -2003) file.
April 19, 2011 at 2:54 pm
OK, just to get things straight.
You are using .xls with the JET provider.
Everything works when you use the sheetname TEST, but the package fails when you use the original sheetname, right?
When you construct the query with the query designer, did you add the table manually or from the Add Table menu?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2011 at 3:42 pm
I typed the SELECT statement manually because I couldn't get a list of "tables" in the query builder.
April 19, 2011 at 11:59 pm
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 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 1, 2011 at 1:30 pm
I don't have a great solution to this problem but I am pretty sure know why you get that error. The name of the sheet is 31 characters. If you change it to 30 characters or fewer then the error should go away.
Unfortunately I do not have other (better) solutions to this at this time.
February 6, 2014 at 2:17 pm
i am getting this problem as well. if i open the file manually and save it then the file works. of course if we wanted to do things manually then we would have different jobs. i have tried to copy as .xlsx using a file system task but with no avail. has anyone else figured this out?
February 7, 2014 at 12:07 am
Can you explain in more detail what do you mean by this bit please?
.. tried to copy as .xlsx ...
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 23, 2014 at 2:22 pm
i tried to change the file extention to see if the file was created in the wrong file format. the way we ended up fixing the issue is the person that created the file changed the sheet name to be less than 30 characters. once he changed the name of the tab the file works with no problem. maybe SSRS allows sheet names of over 30 characters?
June 4, 2021 at 12:01 am
I had similar issue. Check and make sure that the excel file is not protected or read only. Change the protected view settings of the excel file. It might be one of the issue. Hope this helps.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply