This post shows how we can read and export data rom excel starting from nth row.

In SSIS we can read excel data starting from any number of row. Considering a scenario we have a excel file like below screen and we need to read data of Row 3 to Row 14 for Top 10 companies we can do this in SSIS.

To do this I create an Excel Connection manager in my SSIS package. I created a Data Flow Task in the control tab. On Data Flow tab I have following components.


I have put a Data Viewer between Excel Source and Sort. Sort component has no use in my package I simply wanted to show the data in the Data Viewer. I setup my Excel source to read the data from excel file (shown in picture 1). After setting up the Excel Source I went to the Properties window and setup the OpenRowset property value to Sheet1$A3:B14. This is because I want to read the values of Top 10 Companies and their location.


After setting the OpenRowset property I run the package and it shows the 10 rows it has read from Excel file.


In second attempt I want to read the Top 10 Companies of India. To do so I setup the OpenRowset property to Sheet1$A18:B27.


After setting the OpenRowset property I run the package and it shows the 10 rows it has read from Excel file.




We can further export these data to any table or flat file we want to. This shows an example when we have a boundation to read the data from a excel file of a specific row. The same property can be setup programmatically using Script Component.

Republished from Blog by Vikash Kumar Singh [46 clicks].  Read the original version here [8 clicks].