SSIS - Load the Multiple Sheets from Excel File and Load into SQL Table

In this article, I have explained how to load data from multiple Excel sheets to SQL Table or any other destination using SSIS. This can be achieved by using Taks like Foreach Loop Container , Data Flow Task and Components like Excel Source and OLE DB Destination.

Source: Excel File have 3 sheets

Sample Excel

Destination Table:



CREATE TABLE [dbo].[SampleTable](
[Name] [nchar](10) NULL,
[Value] [int] NULL


1. Create a new SSIS project and solution.
• Open SQL Server Business Intelligence Studio (BIDS).
• Select "New Project"
• Select 'Integration Services' template.
• Name of "SSIS" (this will be the name of the overall solution).
• Location of existing folder "C:\SSIS".
• Check the 'Create Directory for Solution' box.
• Click 'Ok' and the Solution will be created.
• In the Solution Explorer window Right click the default package name of 'Package.dtsx' and select rename.
• Use name "SampleExcel.dtsx" and press . Select yes to renaming the pack object as well.


2. Create Connection Managers
Excel Connection Manager
Right click on the connection manager pane and select "Excel Connection Manager"

Excel Connection

OLE DB Connection Manager
Right click on the connection manager pane and select "OLE DB Connection Manager"

SQL Connection

3. From the Toolbox, Double-click or drag the 'Foreach Loop Container' to the Control Flow canvas.
4. From the Toolbox, Double-click or drag the 'Data Flow Task' to the Foreach Loop Container.


5. Create a variable "SheetName" of string type and assign the value " Sheet1$". The variable name should be end with "$" to recognize the sheetname in excel connection manager


6. Edit the Foreach Loop Container, Go to the Collection tab and select the "Foreach ADO.NET Schema Rowset Enumerator" as enumerator


7. In Enumerator Configuration, Select connection drop down box, select new connection,
8. In popup window, Expand provider dropdown, and select "Microsoft Jet 4.0 OLE DB Provider"
9. Browse the file which has multiple Excel sheets from which we will load data.


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


11. In the Collection tab, select the Schema as "Tables". This will configure the Foreach loop container


12. Go to Variable Mapping and map the variable to Sheet Name and Set Index from 0 to 2.


13. Double-Click the Data Flow Task OR click the Data Flow tab to open the task.
14. From the Toolbox Double-click or drag the Excel Source to the Data Flow Task.
15. From the Toolbox Double-click or drag the OLE DB Destination to the Data Flow Task.
16. Double click the Excel Source, Expand Excel Connection Manager dropdown, and select Excel connection manager which is source excel connection
17. Expand the Data access mode , Select "Table Name or View name Variable"
18. Expand the Variable name. Select "User::SheetName"


19. Double click the OLD DB Destination, Select destination connection and destination table name. refer the below screen shot for details


20. In Mappings tab, Map the Source and destination columns shown in the image below


21. Run the package, it will load all the data from source Excel sheets to the destination SQL Table.
22. Data is successfully loaded to Destination Table from three sheets. Refer the below screen shot


Points to remember:
All Excel sheets in the source must have the same structure.


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: