Click here to Skip to main content
Click here to Skip to main content
Go to top

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

, 31 May 2012
Rate this:
Please Sign up or sign in to vote.
Load data present in multiple Excel sheets to an Excel sheet or any other destination.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Smitha_Prabith
Software Developer (Senior)
India India
No Biography provided

Comments and Discussions

 
QuestionNot Working PinmemberNArgade30-Sep-13 3:46 
QuestionUnable to retrieve the correct sheet name Pinmemberrickiz853-Sep-13 17:46 
QuestionSuch a wonderfull Explanation Pinmembervipin jha1-Jan-13 23:01 
Dear Sir,
I am looking to load multiple worksheet from same excel sheet.
please provide the example of same.
 
Regards,
Vipin jha

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140926.1 | Last Updated 31 May 2012
Article Copyright 2012 by Smitha_Prabith
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid