How to load multiple Excel sheets data into one Excel workbook

17
1360

Aim :-  What you are going to Learn is – How to load multiple excel sheets data? The core reason of putting this scenario in Question Laboratory (Q Lab) is that we will show you how to load multiple excel sheets data into a single excel work book without using Foreach loop container in SSIS.

Description :-  I have seen many notifications on Facebook’s MSBI groups regarding this question i.e. How we can load multiple excel sheets data into one excel workbook in SSIS? This question was asked by one guy who faced this scenario in a SSIS Interview. My direct answer was SSIS Foreach Loop Container. But there was a catch in this question. The catch was we have to load the data without using SSIS Foreach loop container.

To be frank I was not aware about the answer for this question and I never faced this scenario before too. I found this post interesting and followed my other passionate members comment. One guy in that group answered this question in a very simple way. Then, I tried it at home and I learned the answer from his comment. So, I thought to make this interesting SSIS Interview question as an article and share with you guys from PhpRing (A Ring of People Helping People) platform.

Steps to Load multiple Excel sheets data into Single Excel workbook without using Foreach Loop container in SSIS

Step 1 :  Create Excel workbook with 3 sheets.

  • Create one excel workbook and fill some dummy data in 3 sheets as the below image.

NOTE Make sure that the format of the data in 3 sheets will be same.

Excel workbook with data in 3 sheets

Step 2 : Create SSIS package to load Data from Excel

  • Create one SSIS (SQL Server Integration Services) package and name it as say “Load excel sheets”.
  • Drag & drop DFT (Data Flow Task) to the control flow pane.
  • Double click on DFT. Now, Drag and drop excel source component from SSIS toolbox to the data flow pane.
  • Double click on Excel source to configure it.
  • Once we click on Excel source, we will see the below screen shot.

Excel Source Editor in SSIS

  • In the above picture, I have created a connection to my source (Excel connection Manager as my source is Excel).
  • Then, Select SQL command as Data access mode.
  • Write below SQL query (copy and paste the same) in SQL command text area.
  • Click on Parse query to test the syntactical errors.
  • Finally click on OK button (If everything works good).

Step 3 :  SSIS package execution to load multiple excel data

  • Instead of configuring destination, I am taking Derived column transformation as temporary destination to show the output.
  • Create a connection between Data flow task (DFT) and Derived column transformation (Temporary destination).
  • I am enabling the Data viewer to see the flow of the data between Excel source and Derived column.
  • Your SSIS package should look like below image.
SSIS package to load multiple excel sheets into one excel workbook
SSIS package to load multiple excel sheets into one excel workbook
  • Finally execute SSIS package to load multiple excel sheets data into one excel workbook.
  • Once you executed the SSIS package, it will look like below image. Green color indicates that package executed successfully and Red color will mean that you have a bad day.
  • In the below screen shot, we can observe that the data from three excel sheets have been read and it has been processed successfully to the next level. This means we achieved our target i.e. To load multiple excel sheets data into one excel workbook.

Successfully loaded data from 3 excel sheets to single excel workbook using SSIS

NOTE  I will only suggest this process, when we have limited number of excel sheets. But, if we have to load more number of excel sheets into a single work book then it will be bit difficult. The reason for this is that we need to write more select statements (equal to number of sheets). So this will make the situation complex for the BI developer.

This is the end of the article on How to load multiple excel sheets data into one workbook in SSIS without using Foreach loop container. If you enjoyed this tip then kindly share it on social websites with your circle. For further queries and valuable feedbacks, write them in the form of comments below.

Edited by Chander Sharma

View articles written by Chander Sharma  

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.2/10 (13 votes cast)
VN:F [1.9.22_1171]
Rating: +12 (from 12 votes)
How to load multiple Excel sheets data into one Excel workbook, 9.2 out of 10 based on 13 ratings
  • D Rakesh

    Really a very useful site for all the learns ……… It keeps increase our knowledge 🙂

    • HI Rakesh,

      We are very glad to here such kind of words from our members.

      I hope you have been learning different things from our site. Keep going…keep learning.

      Thanks,
      Avi

  • Hi Avinash,
    You really rock…this article is very clear and crisp. Keep it by. Kudoos to Chander Sharma too for his efforts to make this site useful to all.

    • Hi Sir,

      Its my great pleasure to hear such a kind words from you.

      Thanks a lot for your appreciation!!!

      I would say a big thanks to #Chander.Thanks for being me a part of #PhpRing.

      I hope my tips might helps to others

      Thanks,
      Avi

  • jeevan

    Hi Avinash,

    Thanks for this article, it is very useful.
    Could you please explain the same by using For each Loop Container also.

    Thanks in Advance.
    Regards,
    Jeevan

    • Hi Jeevan,

      I’m very glad my article was useful to you.
      I can post another article on same , that time I can use foe each loop container.
      Keep learning with #Phpring and stick for more articles.

      Thanks,
      Avi

  • balu

    hi I am new to ssis,I am using BIDS 2005 , may i know how to enable data viewr

    • Hi Balu,
      Thanks for visisting PhpRing.

      I guess, dataviewers are not available in 2005. I never worked that..just i’m assuming.

      Thanks,
      Avi

  • Mallikarjuna

    Hi Avinash,

    Thanks for this article, it is very useful.
    Could you please explain the Scenario like: Load all Sheets which are in Excel Source into Microsoft Office Access .

    Thanks in Advance.
    Regards,
    Mallikarjuna

    • Hi Mallikarujuna,

      Thanks for your comment.

      I’ve seen your question, Do you want me to tell to load the excel sheets data into ms access by using SSIS? IS this your question?

      Thanks,
      Avi

  • Rasul Syed

    Hi Avinash,

    This is really a nice article and thanks for your brief explanation.
    And, could you please provide the step by step process to load the day to day updated DB Table data to the single excel sheet .

    Please let me know for more clarification.

  • Mahesh

    Hi,
    If we have more than One Excel files, then how to load these files into Database?

    • Hi Mahesh,

      You can simply use For each loop container to loop through each file.

      Thanks,
      Avi

  • CH.S.VASU

    Hi,
    Nice article Avinash….
    Thanks for helping me….

  • siva

    hi Avinash sir,
    this is very easy to understand.
    Great work Sir.
    could u plase say How to load multiple excel sheets data into one workbook in SSIS using Foreach loop container.
    Thanks&Regards
    siva