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 **