SSIS Skip Rows in Excel Source file

What?
A quick article on how to skip rows or even specify a range to extract from the Excel file when using as the DataSource.

Why?
If you are simply using a text file as your data source, then the options in the connection manager will let you skip rows and specify column datatypes. You don't get that with Excel but you can still control the data range.

How?
My example is that I have an excel sheet where the first 10 rows are descriptions to the column, and then I have 38 columns ("A" to "AL"):
  1. In the data flow, click on the Excel datasource component so that it's selected.
  2. View the Properties panel for this.
  3. Under Custom Properties » OpenRowset, you should see the name of the Excel worksheet you specified as the datasource (eg. "Sheet1$").
  4. Using the example above:
    • Sheet1$A11:AL12 Selects all data from column A, row 11 up to Column AL, row 12 (so 2 rows will be returned).
    • Sheet1$B11:AL Selects all data from column B, row 11 up to Column AL, row unspecified - used if I don't know how many rows there will be and if I want them all. NOTE: If you don't use column headings, F1 is the first column of the data range and NOT the first column of the data sheet (eg. F1 = B11) !!!
Specifying Excel Data Range in Excel Source Editor

Additional
We add a conditional split task to the data flow which only passes through rows from the Excel sheet which are not blank/null. Browsing the net, I found this is sometimes also used for inserting blank rows to act as dividers.

Microsoft Certified Responses:
  • "In the Excel connection you cannot skip Nth rows."
  • "It is not possible."
I am shocked at how responses to this and opinions vary between "Microsoft Certified Professionals", just a piece of paper and proof you're good at theory IMO, no credit for being the tape monkey that does all the actual work.



Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Joes Word Cloud

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.