SSIS 2005 - Skip first few rows from excel sheet & Import Data to SQL DB

Question

SSIS 2005 - Skip first few rows from excel sheet & Import Data to SQL DB

SQL DBA-909494

SSCommitted

Points: 1605
More actions
August 6, 2009 at 1:09 am

#213130

Hi All,
I am new to SSIS 2005 and don't know VB & need urgent help on how to skip first few rows from the excel sheet. In the excel sheet my data starts from row number 20 & i need to import only the records below after RowNo.20. I tried using below methods after goining through solutions in google
1) SELECT *
FROM OPENDATASOURCE('Provider=Microsoft.Jet.OLEDB.4.0','Data Source="D:\Test \file1.xls";Extended Properties="Excel 8.0;HDR=No"','SELECT * FROM [Sales_Forecast$a19:au19]');
I even tried the same using openrowset.
2)Tried pasting the above command in Excel source under OpenRowset-->Data Acess Mode -->SQL command, but there I get error near from clause. Not able to trace it.
It would be really helpful if you can provide me a method to do this skipping in SSIS 2005 or give me a tool within SSIS which can do this task.
----------------------------------------------------------------
**"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

You have to setup an Excel Connection and then use similar SQL statement to the one you use with the cells range. It should work, but keep in mind you have to execute your package in 32bit mode if you are running on 64bit OS.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

You can also use an asynchronous script component. I'm attaching a complete example.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SQL DBA-909494 SSCommitted Points: 1605 More actions · Answer 3

Thanks Jack.

But I am not getting on what code/expression to use within script task . If you could help me it would be great. I have no idea of SSIS & working on this for the first time.

----------------------------------------------------------------
**"There is only one difference between dream and aim. Dream requires Soundless sleep to see…Where as Aim Requires Sleepless Efforts to Achieve":-) **

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Well, it's a script component (there is a difference) and I attached a zip file that contains an excel file and an SSIS package that reads the excel file and processes it using a script component, bypassing the first 20 rows. Have you looked at the package I attached?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question