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.
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.
- 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.
- 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.
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