SSIS: How can you read data from hidden Excel worksheets using SSIS?

[Red_Wizard_Shot_The_Food]

I'm trying to write an SSIS package that will read data from 3 hidden sheets in a workbook. The idea is that other departments can input data into 3 non-hidden sheets, that have a user friendly layout along with instructions and examples, etc. And the 3 hidden sheets then take this data and hold it in a basic table format with a single header row. To simplify importing we want to just read the data from these hidden sheets... but a SSIS's standard Excel connection / Excel Source can't seem to see these hidden sheets. Is there a way to get SSIS to see these hidden sheets without making them visible in the workbook itself? Thanks.

[Wendelius]

When referring to the worksheet, try using syntax (with correct path): ...\MyExcelWorkbook.xls![Some hidden worksheet]

The need to optimize rises from a bad design.My articles[^]

[Red_Wizard_Shot_The_Food]

Thank you, that looks like it may be halfway to working.... now I'm getting a message about the source being read-only when I try to do anything with my Excel Source object now :( There is no password protection, currently on the workbook or the sheets.

[Wendelius]

Red_Wizard_Shot_The_Food wrote:

now I'm getting a message about the source being read-only

Red_Wizard_Shot_The_Food wrote:

There is no password protection, currently on the workbook or the sheets

Is the file read-only? If it is, could you remove that. Another possibility could be that it resides in a directory where you don't have write privileges or at file level, perhaps you don't write privilege to the file.

The need to optimize rises from a bad design.My articles[^]