Load Multiple Excel Files and Worksheets with SSIS

I was asked to load multiple Excel files, each with multiple Excel worksheets. I found a very easy way to complete this in SSIS using a Script Task. This article will explain how I did this.

For this example, I have two Excel files, File1.xlsx and File2.xlsx, as shown here:

Each Excel File has three sheets, which are sheet1, sheet2, and sheet3. Each sheet has two columns, Filename and sheetname and I have inserted one row in each sheet as shown below

Each Excel worksheet should have same schema for any data that is stored.

To load the