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: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience 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 bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.