Here is one possible way of doing this based on the assumption that there will not be any blank sheets in the Excel files and also all the sheets follow the exact same structure. Also, under the assumption that the file extension is only .xlsx
Following example was created using SSIS 2008 R2 and Excel 2007. The working folder for this example is F:\Temp\
In the folder path F:\Temp\, create an Excel 2007 spreadsheet file named States_1.xlsx with two worksheets.
Sheet 1 of States_1.xlsx contained the following data
Sheet 2 of States_1.xlsx contained the following data
In the folder path F:\Temp\, create another Excel 2007 spreadsheet file named States_2.xlsx with two worksheets.
Sheet 1 of States_2.xlsx contained the following data
Sheet 2 of States_2.xlsx contained the following data
Create a table in SQL Server named dbo.Destination using the below create script. Excel sheet data will be inserted into this table.
CREATE TABLE [dbo].[Destination](
[Id] [int] IDENTITY(1,1) NOT NULL,
[State] [nvarchar](255) NULL,
[Country] [nvarchar](255) NULL,
[FilePath] [nvarchar](255) NULL,
[SheetName] [nvarchar](255) NULL,
CONSTRAINT [PK_Destination] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO
The table is currently empty.
Create a new SSIS package and on the package, create the following 4 variables. FolderPath will contain the folder where the Excel files are stored. FilePattern will contain the extension of the files that will be looped through and this example works only for .xlsx. FilePath will be assigned with a value by the Foreach Loop container but we need a valid path to begin with for design time and it is currently populated with the path F:\Temp\States_1.xlsx of the first Excel file. SheetName will contain the actual sheet name but we need to populate with initial value Sheet1$ to avoid design time error.
In the package’s connection manager, create an ADO.NET connection with the following configuration and name it as ExcelSchema.
Select the provider Microsoft Office 12.0 Access Database Engine OLE DB Provider under .Net Providers for OleDb. Provide the file path F:\Temp\States_1.xlsx
Click on the All section on the left side and set the property Extended Properties to Excel 12.0 to denote the version of Excel. Here in this case 12.0 denotes Excel 2007. Click on the Test Connection to make sure that the connection succeeds.
Create an Excel connection manager named Excel as shown below.
Create an OLE DB Connection SQL Server named SQLServer. So, we should have three connections on the package as shown below.
We need to do the following connection string changes so that the Excel file is dynamically changed as the files are looped through.
On the connection ExcelSchema, configure the expression ServerName to use the variable FilePath. Click on the ellipsis button to configure the expression.
Similarly on the connection Excel, configure the expression ServerName to use the variable FilePath. Click on the ellipsis button to configure the expression.
On the Control Flow, place two Foreach Loop containers one within the other. The first Foreach Loop container named Loop files will loop through the files. The second Foreach Loop container will through the sheets within the container. Within the inner For each loop container, place a Data Flow Task that will read the Excel files and load data into SQL
Configure the first Foreach loop container named Loop files as shown below:
Configure the first Foreach loop container named Loop sheets as shown below:
Inside the data flow task, place an Excel Source, Derived Column and OLE DB Destination as shown below:
Configure the Excel Source to read the appropriate Excel file and the sheet that is currently being looped through.
Configure the derived column to create new columns for file name and sheet name. This is just to demonstrate this example but has no significance.
Configure the OLE DB destination to insert the data into the SQL table.
Below screenshot shows successful execution of the package.
Below screenshot shows that data from the 4 workbooks in 2 Excel spreadsheets that were creating in the beginning of this answer is correctly loaded into the SQL table dbo.Destination.
Hope that helps.