Alex Rivera | Logout

How to loop through Excel files and load them into a database using SSIS package?

Asked 2011-09-14T05:39:41.007
33

I need to create an SSIS package for importing data from multiple Excel files into an SQL database. I plan on using nested Foreach Loop containers to achieve this. One Foreach File Enumerator and nested within that, a Foreach ADO.net Schema Rowset Enumerator

Problem to consider: Sheet names are different between excel files but structure remains the same.

I have created an Excel Connection Manager, but the Schema Rowset Enumerator is not accepting the connection manager in the Enumerator configuration.

After researching, I found that you can use the Jet Ole db provider to connect to an excel file. However, I can only specify Microsoft Access Database Files as the data source. Attempting to insert an Excel File as the data source fails

After more research I found that you can use the Odbc Data Provider with a connection string instead of a DSN. After inserting a connection string specifying the Excel file this also failed

I have been told not to use a Script Task to accomplish this and even after trying a last ditch effort to extract data from sheets be accessing the sheets by index I found that the index for the sheets in the different excel files are different

Any help would be greatly appreciated

excel ssis
Edit
Report

1 Answer

92

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

States_1_Sheet_1

Sheet 2 of States_1.xlsx contained the following data

States_1_Sheet_2

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

States_2_Sheet_1

Sheet 2 of States_2.xlsx contained the following data

States_2_Sheet_2

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.

Empty table

Create a new SSIS package and on the package, create the following 4 variables. Fo

answered 2011-09-14T07:21:51.953

Your Answer