**Aim : **How can we fetch data from excel using SSIS from a particular row or middle of the excel sheet. **Description : **During my recent project development, I faced this interesting scenario. My client sent me the data in excel file and told me to export the data from that given Excel to local SQL Server machine. Final output was to create a report according to the data present in database. The problem was we had data at different places in the same excel sheet. I mean to say the data was not present continuous in excel file. To put my point more clearly I will explain it in simple way. We need to fetch the data from the 10^{th} row onwards and need to ignore first 9 rows. I was very curious at that time and started thinking about how to ignore the first 9 rows and how to fetch the data from 10^{th} row on words. My brain was not helping me in finding the solution as I forgot to eat almonds that day. I then opted the best option i.e. I started googling for the solution. After searching too many links I found the right solution to my question i.e. fetch data from excel using SSIS from a particular row. So, I’m sharing it with you also. The following are the steps which we need to follow while creating an ssis package to fetch data from excel using SSIS from a particular row. Let’s see this interesting solution.

**Steps to Fetch Data from Exel using SSIS**

**Step 1 – Excel Source.** Below is the excel sheet which contains the data at various different rows. But, we need to fetch it from 10^{th} row onwards. **Step 2 – SSIS Package Configuration.**

- Create one SSIS package and name it as say,
**Sample.dtsx.** - Now, Drag and drop one Dataflow task.
- Now, Drag and drop one excel source.
- Double click on Excel source to configure excel properties.
- Create excel connection manager and give the path of that excel file.
- Once you are done with the configuration of excel source. It looks like below picture.
- Click on “OK” to finish the excel source configurations.

**Note :-** Here I’m not going to show the configuration part of excel source. I am sure you all must be expert in configuring excel source. **Step 3 – Excel Source property window.**

- Now, we are going to play a small trick here to fetch data from excel using SSIS starting from the 10
^{th}row (specific row). - Select Excel source and press “F4” to get properties window. Once you done the above step it will bring up the properties window. Below is the screen shot for same. Now go to Custom properties option. Here we can see that the value for OpenRowset option is the name of the excel sheet i.e. Sheet1$.

- We will change this
**OpenRowset**property value =**A10:D20.** - In this, A10 will indicate the starting position of the data and D20 indicates the ending position of the data.
- We can also observe the same in the source of excel sheet I.e. where our data starts and where it ends. It started at A10 and Ended at D20. This is the only reason I’ve taken the same in OpenRowset property. You can configure it as per your data and need.
- One important question is if we have huge data in our excel sheet. For example, if we have lacks of rows. In this case, it is difficult to find the end point and it may take a long. So, simple solution is – Your data starts at A10 and ends up with same column D but we don’t know the exact number of rows when the data ends up in column D. So, for this situation you can set OpenRowset property to “
**OpenRowset=A10:D**”.

**Step 4 – Excel Source OpenRowset property configuration.**

- In our case we can clearly see that the data starts at A10 and Ends at D20. So, we will consider the same range.
- Now we will configure our OpenRowset property =A10:D20. The windows will look like image below.
- Simply click on “OK” and you are done with the solution to this interesting problem. That’s it.

**Step 5 – OLEDB destination.**

- Drag and drop OLEDB destination to get the output.
- Create a connection between Excel source and OLEDB destination.
- Configure your OLEDB destination.
- Launch time now. Run the sample.dtsx package.
- To verify the output, Go to SQL server and open table EXCEL_FILE.

**Conclusion :** In the above result you can clearly observe that the employee with the name Seshu was skipped. The reason behind this is – Employee named “Seshu” record was not in the range of **A10:D20**. That’s why it is not considered in the output. With this we come to an end of this article, I hope you liked this article on how to fetch data from excel using SSIS from a particular row. you can give your ratings and comments below. We will highly appreciate your feedback.

Great job AVINASH.Thank you……

Hi Shameer,

I’m very glad to see you comment. Hope you find it useful.

Thanks,

Avi

Your scenario is most useful for me

Hi Vasu,

Thanks a lot for your comment. Glad to know my tip is useful to you.

Thanks,

Avi

nice representation avinash!!!!…………

Hi Sathish,

I’m very glad to see your comment. Thank you!!

Thanks,

Avi

This article is very helpfull.

Thanks for providing clearly step by step

Hi,

I’m very glad to hear. Staytune for more!

Thanks,

Avi

Your articles are very good and easy to understand.Thanks a lot for sharing your knowledge.

We appreciate your hard work .

Hi Surender,

Thanks for visiting Phpring and my articles too. Its my pleasure to hear your kind words.

Yes, We are here to help people.

Keep learning and stick with PhpRing for more tips.

Thanks,

Avi

Hi Avinash,

Nice example. Can we do it different way instead of change the openrowset value from Sjheet1 to A10:D20. We can directly write on sql commad in data access mode in excel source to get the data from any rows and column : Select * from [Sheet1$A10:D20].

Please correct me if I am wrong.

Thanks

Ashish Kumar !!!

Hi Ashish,

Yep, you can do like that.

Thanks,

Avi

Hi Avinash,

in the same scenario i need a requirement like only need empid and sal columns data.

Thanks

Nagarjuna.

Comments are closed.