Session 5 : How to Load Multiple Excel Sheets AnandThursday, January 8, 2015 0 comments

Session 5 : How to Load Multiple Excel Sheets


Source: Excel File have 3 sheets



Destination Table:

 
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

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

GO

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”



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



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.