How to load data from multiple Excel sheets to any destination in SSIS

Introduction

In this article, we will load data present in multiple Excel sheets to an Excel sheet or any other destination. For example, we have a source as an Excel, which has data about an organisation. Suppose this Excel sheet is storing data in different sheets. Like we have sheets as "Accounts", "IT", "Admin", etc.

Now our purpose is to load this into the destination. How can we do this using SSIS?

Using the code

1. Create a package in SSIS and rename it "LoadingExcelSheets".
2. Now create a variable varSheetName of string type and assign the value "IT$". Ensure the variable name ends with "$".
3. This will help recognize the sheets in our Excel.
4. Now add a ForEach loop container and rename it as "FELC_LoadingExcels".
5. As we will be loading data from multiple sheets, we need to configure our Foreach loop container as below.
6. Double click the Foreach loop container, go to the Collection tab.



7. In the Collection tab, select "Foreach ADO.NET Schema Rowset Enumerator".
8. When we add this enumerator, we get many different configurations in the Enumerator Configuration.
9. Now select Connection from the Enumerator Configuration.
10. From the dropdown, select New connection.



11. Click on the New button of the Configure ADO.NET Connection Manager.
12. This will popup another window, which is nothing but the connection manager window.
13. Now, in the provider dropdown, expand ".NET Providers for OleDb" and select "Microsoft Jet 4.0 OLE DB Provider".
14. Refer the below screenshots.



15. Browse the file which has multiple Excel sheets from which we will load data.



16. Go to the "All" tab, go to the "Advanced" tab, and set the Value as "Excel 8.0" for "Extended Properties".



17. Test the connection.
18. In the Collection tab, select the Schema as "Tables". This will configure the Foreach loop container.



19. Now add a Data flow task and rename it DFT_LoadingExcelSheets.
20. In the data flow, add an "Excel Source" and rename it Src_Test_123.
21. Double click on the DFT, add a new connection manager for the Excel, and rename it src_Test_123_Conn.



22. From "Data_Access_Mode", select "Table name or view name variable" as shown in the below image.



23. Now from the "Variable_name" dropdown, select the variable which we created as "LoadingExcelSheets".
24. This will help the foreach loop to loop through all the sheets in Excel.
25. Now configure the destination as Excel.
26. This will configure the DFT.
27. Now execute the package, this package will load all the data from our source Excel sheets to the destination Excel sheet.
28. Note here, data is loaded into one Excel sheet in the destination...

Please note, the structure of the Excel sheets must be same. I.e., if the first sheet has the structure:

ID Age Salary 
1   31  5000

all other Excel sheets in the source must have the same structure.