SSIS Excel to SQL import -- First 6 rows of the file contains header-related information

Question

I am working on importing thousands of rows(120K) of data from an excel file into a SQL Server. Now I am trying to use SSIS to accomplish this but I immediately ran into some problem considering the excel template that the excel file is built with does not seem to contain the Header in just the first row(about the first 6 rows contain header information). How do I solve for this problem using the SSIS Data flow task in BIDS to handle the imports? Or would it be better to read the lines via direct read of each row from the Excel document?

Other information that I think will be helpful here is

a) I am trying to find an alternative to reading each excel row from a windows client application, and then writing the data to the database line by line

b) I have about 4 excel worksheets in my excel document

c) If there is a way to read line by line from excel and have it done efficiently, rather than use SSIS from Windows client which I am not that familiar with. I will appreciate any suggestions as to how

d) the particular header of my excel document resides on line 7, and I have it minimized sine the information is only to be for my backend need.

Solution

You can set OpenRowset property of Excel Data Source (Properties window, OpenRowset in Custom Properties section) to value similar to Sheet1$a6:j, where a is first column with your data, j is last column with data and 6 is usually row with header just before data. Data should start in next row. You can also set last row to be read by setting value similar to Sheet1$a6:j20.
Note that first given row is sometimes treated as header row and sometimes as first row with data. For example with excel:

when I set OpenRowset to Sheet1$a3:j third row is treated as header row:

but when I set OpenRowset to Sheet1$a3:j8 this row is treated as first data row:

Strange.

Comments

[Kobojunkie]

I am trying to build a windows client front end that will allow user select an excel file of a particular format and have the data from the file loaded and processed

[Kobojunkie]

When I set the OpenRowSet to Sheet1$a6:he, I get the error an error. I am not sure why but just so you know, I have about 4 sheets in my excel document that I would also like to tie to database

[Kobojunkie]

By setting OpenRowSet, I see i am unable to specify the Excel Sheet to map. How do I deal with that issue?

[Piotr Sobiegraj]

@Kobojunkie what error do you have? What's name of sheet to be read? I you've changed sheet names from default Sheet1 to Sheet3 you should use this name in expression, ie SheetName$a6:he