SSIS - Read Multiple Sheets from Excel File and Load into Table

Scenario: 

We have our source Excel file that has sale history. Each sheet will has data for a year. We can get data for any number or years. In this example we have data for three years. We need to load this data to our destination table.

Source Excel File looks like this

Destination Table definition:
CREATE TABLE dbo.SaleHisTory
  (
     ID             INT IDENTITY(1, 1),
     SalePersonName VARCHAR(100),
     SaleCountry    VARCHAR(50),
     SaleAmt        INT,
     SheetName      VARCHAR(100)
  ) 

Solution : 

We will be using Foreach Loop Container to perform this requirement. Let's start with step by step approach

Step 1: 
Create connection to our Excel source file as shown below


Choose Microsoft Office 12.0 Access Database Engine OLE DB Provided as shown below

Press Ok as shown above in number 3.  Provide the File Path as shown below

Click on All and Provide values as shown below

We are all done with configuring our Excel File Connection. 

Step 2: 
Bring For each loop container to the Control Flow Pane and configure as shown below 
Enumerator : Foreach ADO.NET Schema Rowset Enumerator
Connection String : The OLE DB Connection String that we have created in Step 1
Schema : Tables As we are interested to get the names of sheets

Go to Variable Mapping and map the variable to Table_Name as shown below

Set Index from 0 to 2 as Table_Name is on third number in List


Step 3: 
Bring Data Flow Task to Control Flow pane inside Foreach Loop Container. Open Data Flow by double Clicking and then bring ADO.NET Source as shown below and configure. One thing to notice here, we do not have option to provide variable in which we have saved our SheetName , For now Choose any sheet you like as all Sheets in Excel are with same metadata.

Drag Derived Column Transformation to Data Flow pane and Connect ADO NET Source to it. Perform data conversation according to destination columns and also generate new column DER_SheetName by using SheetName variable.

After that bring OLE DB Destination and connect Derived Column Transformation to it and map the columns.


Step 4: 
Till here our package is ready but only Table Name(SheetName) is hard coded in our ADO NET Source. We want to change it to SheetName Variable so with each iteration it should change so we can load all the Excel sheets.
To do that , Click on Data Flow Task and go to properties and then Expression and configure as shown.

Set the data flow property Delay Validation=True so it should not validation as sheet names will be provided while execution not at start of package validation.

Final Output :
Execute package and check the destination table if all the records from all excel sheets are loaded successfully.
Data is successfully loaded to dbo.SaleHistory Table from three sheets 2011,2012,1013 as shown below.



Video Demo for " How to Load Multiple Sheets to a SQL Server Table"




Check out our other posts/ Videos related to Excel Source and Destination
  1. How to Load Data from Excel Files when Number of Columns can decrease or order is changed in Excel Sheet
  2. How to Load Only Matching Column Data to SQL Server Table from Multiple Excel Files (Single Sheet per file) Dynamically in SSIS Package
  3. How to Load Excel File Names with Sheet Names ,Row Count,Last Modified Date, File Size in SQL Server Table
  4. How to Load Multiple Excel Files with Multiple Sheets to Single SQL Server Table by using SSIS Package
  5. How to Load Matching Sheets from Excel to Table and Log Not Matching Sheets Information in SQL Server Table
  6. How to create Table for each sheet in Excel Files and load data to it dynamically in SSIS Package
  7. How to Create Table per Excel File and Load all Sheets Data Dynamically in SSIS Package by using Script Task 
  8. How to create CSV file per Excel File and Load All Sheets from Excel File to it in SSIS Package
  9. How to Create CSV File for Each Excel Sheet from Excel Files in SSIS Package
  10. How to Load Excel File Name and Sheet Name with Data to SQL Server in SSIS Package
  11. How to Import data from Multiple Excel Sheets with a pattern of sheet names from Multiple Excel File in SSIS Package
  12. How to import Data from Excel Files for specific Sheet Name to SQL Server Table in SSIS Package
  13. Load Data To Tables according to Excel Sheet Names from Excel Files dynamically in SSIS Package
  14. How to Load Excel Files with Single/ Multiple Sheets to SQL Server Tables according to Excel File Name Dynamically
  15. How to Read Excel Sheet Data after Skipping Rows in SSIS Package by using Script Task 
  16. How to read data from Excel Sheet and Load to Multiple Tables by using Script Task in SSIS Package
  17. How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package
  18. How to create Excel File Dynamically for Stored Procedure Results in SSIS Package by using Script Task
  19. How to Export SQL Server Tables from Database to Excel File Dynamically in SSIS Package by using Script Task
  20. How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task
  21. How to Load All CSV Files to Excel Sheets ( Sheet Per CSV) in single Excel File in SSIS Package
  22. How to Load All CSV Files to Single Excel Sheet with File Names in an Excel File Dynamically in SSIS Package
  23. How to Create Sample Excel file with Sheet from each table with Top 1000 Rows per sheet in SSIS Package
  24. How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package
  25. How to split large table data into multiple Excel Sheets on Single Excel File by using SSIS Package
  26. How to Export All tables of a database to Excel Files with Date-time in SSIS Package

26 comments:



  1. Soma pill is very effective as a painkiller that helps us to get effective relief from pain. This cannot cure pain. Yet when it is taken with proper rest, it can offer you effective relief from pain.
    This painkiller can offer you relief from any kind of pain. But Soma 350 mg is best in treating acute pain. Acute pain is a type of short-term pain which is sharp in nature. Buy Soma 350 mg online to get relief from your acute pain.

    https://globalonlinepills.com/product/soma-350-mg/


    Buy Soma 350 mg
    Soma Pill
    Buy Soma 350 mg online



    Buy Soma 350 mg online
    Soma Pill
    Buy Soma 350 mg

    ReplyDelete


  2. Soma pill is very effective as a painkiller that helps us to get effective relief from pain. This cannot cure pain. Yet when it is taken with proper rest, it can offer you effective relief from pain.
    This painkiller can offer you relief from any kind of pain. But Soma 350 mg is best in treating acute pain. Acute pain is a type of short-term pain which is sharp in nature. Buy Soma 350 mg online to get relief from your acute pain.

    https://globalonlinepills.com/product/soma-350-mg/


    Buy Soma 350 mg
    Soma Pill
    Buy Soma 350 mg online



    Buy Soma 350 mg online
    Soma Pill
    Buy Soma 350 mg

    ReplyDelete
  3. We Offered Python Training in Gurgaon with Top Trainers at Low Cost. This Python Course will help you to Learn Python Programming from scratch in a Practical Manner. Our Python Training teaches you how to write Python Coding for Data Science, Machine learning algorithms, web scratching, Deep learning, and AI Techniques. Through this Python Classroom Training in Gurgaon, you will get 100% Placement from our tied up company.
    For More Info: Python Training in Gurgaon

    ReplyDelete
  4. i was just browsing along and came upon your blog. just wanted to say good blog and this article really helped me.
    excel institute in gurgaon

    ReplyDelete
  5. Courseafter12th institute offers Best Web Designing in Delhi. We offer website design education for bringing experts. Call Now & GET SCHOLARSHIP DISCOUNT ON DIPLOMA COURSES at 8860222625.
    Best Web Designing in Delhi
    Best E-accounting Institute in Delhi
    Best Tally Course in Delhi

    ReplyDelete
  6. It is actually a great and helpful piece of information about Java. I am satisfied that you simply shared this helpful information with us. Please stay us informed like this. Thanks for sharing.

    Java training in chennai | Java training in annanagar | Java training in omr | Java training in porur | Java training in tambaram | Java training in velachery

    ReplyDelete
  7. I feel SSIS is the best and most useful aspect of providing and finding solutions about some complex IT problems.

    SSIS Postgresql Read

    ReplyDelete
  8. You have even succeeded to make it reasonable and peaceful to read. Will surely come and visit this blog more often. Thanks for sharing. Otherwise if anyone want to Learn Advanced Excel So You Contact Here-+91-9311002620 Or Visit Website- Click Here- Advanced Excel Training Institute in Delhi

    ReplyDelete
  9. Very informative blog, If you want on Online Excel Database than use Turnao's service they offer free service for personal/ single user, Turnao is web-based application used to convert excel spreadsheets to online databases.

    ReplyDelete
  10. you have written an excellent blog. I learned something new from your Blog. Keep sharing valuable information.
    Advanced Excel Training in Chennai
    Excel Training in Chennai

    ReplyDelete
  11. The next time I read a blog, I hope that it doesnt disappoint me as much as this one. I mean, I know it was my choice to read, but I actually thought you have something interesting to say. All I hear is a bunch of whining about something that you could fix if you werent too busy looking for attention. advanced excel training in gurgaon

    ReplyDelete
  12. I really appreciate your hard work you put into your blog and detailed information you provide. Further More Information About MIS training institute in Delhi Contact Here-+91-9311002620 Or Visit Website- https://www.htsindia.com/Courses/business-analytics/MIS-training-instiute-in-delhi

    ReplyDelete
  13. Thank you for sharing an amazing & wonderful blog. This content is very useful, informative and valuable in order to enhance knowledge. Keep sharing this type of content with us & keep updating us with new blogs. Apart from this, if anyone who wants to join the Tally Training institute in Delhi, can contact 9311002620 or visit our website-
    Tally Training Institute in Delhi | No-1 Tally Training Course in Delhi (htsindia.com)

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. T-Shomedia is a Manchester web design and development company that offers a range of services to businesses and individuals. We specialise in custom web design, online marketing, SEO, and online advertising. We have experience working with a variety of different industries, so we can provide you with the perfect solution for your website or online presence. Contact us today to learn more about our services!

    ReplyDelete
  16. Thanks for allowing me to talk about a Full stack training in delhi. For the Best career in Full Stack Developer, I recommend Skillslash is recognized as the Full stack developer course in Delhi. We cover all the essential topics in Full stack like frontend, backend and DSA etc.

    ReplyDelete
  17. Looking for a Full stack developer course in Hyderabad? Look no further than Innomatics Research Labs. With its industry-relevant curriculum and experienced faculty, Innomatics has established itself as a leader in data science education. Innomatics Full stack program covers a wide range of topics.

    Full stack developer course in Hyderabad

    ReplyDelete