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