Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to import an Excel spreadsheet into SQL Server 2012 using SSIS. There is only one sheet in the file. The file is a SalesForce export. I believe the Sheet name changes depending on when the export is performed. So i have a nested foreach containers to go through firstly the .xls files then the sheets within so that i don't have to specify the sheet name. The problem is that SSIS seems to think there is more than one sheet in the file. One called 'report1352373663719 1 $' and one called 'report1352373663719 1 $'_

I've opened the file in Excel and there's only one sheet on there and there doesn't appear to be any hidden sheets.

Interestingly i tried to import the 'report1352373663719 1 $'_ sheet using the bog standard import and export wizard and it errors with The Microsoft Jet database engine could not find object 'report1352373663719 1 $'_

The first sheet detected gets imported without any problems but my package errors when trying to process the 2nd (non-existent) sheet.

Does anyone have any idea as to how i can stop my package from erroring?

** Update **

So it seems that this is something to do with the data filter options in Excel. If you create a new sheet you don't get the _ sheet. If you add a filter to the top row and save your sheet, the _ sheet appears. If you then remove the filter again, re-save your sheet the _ sheet remains. Frustrating to say the least.

** End of update **

Thanks,

Simon

share|improve this question
    
So your issue is resolved? – Zane Jan 24 '13 at 17:07
    
Not really. I'd still like my package not to fail when importing the ghost sheet. I've considered script tasks and such to copy data from one file to a new file but Excel is not installed on the server where this file is processed so i can't do that. – Simon Osborne Jan 24 '13 at 18:50
1  
What version of excel are you saving these files as? – Zane Jan 24 '13 at 19:11
1  
Excel 2003-2007. It may become a non issue now. I'm asking for the export to arrive as CSV instead of XLS. It'll be easier to handle. – Simon Osborne Jan 25 '13 at 10:09
5  
Typically importing from a flat file with integration services is faster and more reliable so if you can get it that way I would certainly recommend it. – Zane Jan 25 '13 at 15:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.