Take the 2-minute tour ×
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. It's 100% free, no registration required.

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.