Search code examples
ssis-2008

Syntax for excel source specific range with spaces in sheet name

I would like to read a specific range on worksheet in an xlsx file with SSIS 2008.

To open the whole sheet, I need to add 'Report Data 1' in the "OpenRowset" field under advanced settings in the excel data source panel. Now, I need everything from line 3 (including headers), so I assumed 'Report Data 1'$A3:K would be the good choice, but didn't work. I tried a few other combinations, but none of these work:

'Report Data 1$A3:K' [Report Data 1]$A3:K '[Report Data 1]$A3:K'

I also tried with fixed amount of rows, didn't help.

The error I'm getting is: Error at Data Flow Task [Excel Source [9]]: Opening a rowset for "'Report Data 1'$A3:K100" failed. Check that the object exists in the database.

I tried with another test sheet and if there's no space in the sheet name, I can read the range with Sheet1$A3:K4 in the OpenRowset property. What is the proper syntax for the openrowset property?

Solution

  • It could be a source problem of setting, in Excel Source Editor choose Data acces mode: SQL Command and write in SQL command text: 

    SELECT * FROM [Report Data 1$A3:K]