Click here to Skip to main content
15,867,453 members
Articles / Productivity Apps and Services / Microsoft Office / Microsoft Excel
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
31 May 2012CPOL2 min read 98.1K   4   4
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. Image 1

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

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

  18. Browse the file which has multiple Excel sheets from which we will load data.
  19. Image 4

  20. Go to the "All" tab, go to the "Advanced" tab, and set the Value as "Excel 8.0" for "Extended Properties".
  21. Image 5

  22. Test the connection.
  23. In the Collection tab, select the Schema as "Tables". This will configure the Foreach loop container.
  24. Image 6

  25. Now add a Data flow task and rename it DFT_LoadingExcelSheets.
  26. In the data flow, add an "Excel Source" and rename it Src_Test_123.
  27. Double click on the DFT, add a new connection manager for the Excel, and rename it src_Test_123_Conn.
  28. Image 7

  29. From "Data_Access_Mode", select "Table name or view name variable" as shown in the below image.
  30. Image 8

  31. Now from the "Variable_name" dropdown, select the variable which we created as "LoadingExcelSheets".
  32. This will help the foreach loop to loop through all the sheets in Excel.
  33. Now configure the destination as Excel.
  34. This will configure the DFT.
  35. Now execute the package, this package will load all the data from our source Excel sheets to the destination Excel sheet.
  36. 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)


Written By
Software Developer (Senior)
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionAdditional crucial step Pin
Peter Ostrovsky8-May-15 8:38
Peter Ostrovsky8-May-15 8:38 
QuestionNot Working Pin
NArgade30-Sep-13 3:46
NArgade30-Sep-13 3:46 
QuestionUnable to retrieve the correct sheet name Pin
rickiz853-Sep-13 17:46
rickiz853-Sep-13 17:46 
QuestionSuch a wonderfull Explanation Pin
vipin jha1-Jan-13 23:01
vipin 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    Praise Praise    Rant Rant    Admin Admin   

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