SSIS package to query an Excel data source, ignore first 10 rows.

I have this requirement to pull data from an excel spreadsheet and perform some transformationms and  convert it to a csv file. Now the problem is The excel file has the useful data starting from some 7th row and all the initial rows have some information and metadata.
I need to query the excel spread sheet.
I have tried this query,
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Documents and Settings\ \Myfile0282010.xls;IMEX=1',
                'SELECT * FROM [Sheet2$]')

This query doesnt work. I get an error which says " sysntax error in the FROM clause"
It gives me an error even when I try it on a sheet with data starting from the second row, and column names on the first row.  
Please help. I use a data flow task and use an excel connection manager and use the option, "Sql command" on the data access mode
Appreciate your response
Thanks
sriscanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Reza RadConsultant, TrainerCommented:
could you upload your excel file here?
0
sriscanAuthor Commented:
I guess I dont need to use "openrowset" at all when I use Excel source connection manager
I tried this query and its working great
SELECT    F3,F18
FROM    [FAC_Per Risk$B8:S50]

but now the problem is I must automate the package and each month I have different nmber of records. I would know the starting position i.e. here B8 which is fixed every month but the location S50 is not constant i.e. next month I could have something like 80 rows. and then at the end I have one row empty completely and then I have subtotals and some comments which I should not be including in the ETL. please help how I could grab the data only until the empty row at the end.
Thanks
0
da-zeroCommented:
I would try to read everything in the sheet as text, and then filter out unwanted rows with the conditional split.
0
carsRSTCommented:
1.  Create  a new  table that matches the columns in your Excel sheet (take note on the data types)
2.  In your SSIS package, iImport the Excel workbook in to this new table
3.  And then query from the table as opposed to the worksheet.  

In your SSSIS package, before you import, clear out the table, so as to not have data from the previous run.

While it is possible to query a worksheet, it's not really a good idea.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.