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 data, we start by creating a table with the structure that matches each sheet:

CREATE TABLE dbo.Excelfiledata
( 
[Filename] varchar(50),
[Sheetname] vawrchar(50)
);

Next we create an SSIS package. Open Visual Studio and create a new Integration Services project.

In the project, create two variables of the string datatype, as shown below. Enter the folder path for the Excel files in the FolderPath Variable and name of the table created above in the TableName variable.

In the Connection Manager,  add a new ADO.Net connection and provide the instance name and database name, along with any other credentials needed.

Note: Name the connection as Sqlconnection. This is shown below.

Next, we drag a Script Task onto the Control Flow desktop.

Open the Script Task and in the Script section, we need to edit the Readonlyvariables property. Enter the variables created above, with the User::variablename structure. This is shown in the image below.

Select "Edit Script" and copy the following C# code into the edit box. This code will loop through all the files, open each one, and then loop through each sheet.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
public void Main()
{
            String FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();
            String TableName = Dts.Variables["User::TableName"].Value.ToString();
            var directory = new DirectoryInfo(FolderPath);
            FileInfo[] files = directory.GetFiles();
            //Declare and initilize variables
            string fileFullPath = "";
            //Get one Book(Excel file at a time)
            foreach (FileInfo file in files)
            {
                fileFullPath = FolderPath + "\\" + file.Name;
                //Create Excel Connection
                string ConStr;
                string HDR;
                HDR = "YES";
                ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
                //Get sheetnames
                OleDbConnection cnn = new OleDbConnection(ConStr);                
                cnn.Open();
                DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string sheetname;
                sheetname = "";
                foreach (DataRow drSheet in dtSheet.Rows)
                {
                    if (drSheet["TABLE_NAME"].ToString().Contains("$"))
                    {
                        sheetname = drSheet["TABLE_NAME"].ToString();
                    }
                    //ADO.NET Connection
                    SqlConnection myADONETConnection = new SqlConnection();
                    myADONETConnection = (SqlConnection)(Dts.Connections["Sqlconnection"].AcquireConnection(Dts.Transaction) as SqlConnection);

                    //Get data from Excel Sheet
                    OleDbCommand oconn1 = new OleDbCommand("select * from [" + sheetname + "]", cnn);
                    OleDbDataAdapter adp1 = new OleDbDataAdapter(oconn1);
                    DataTable dt1 = new DataTable();
                    adp1.Fill(dt1);
                   
                    //Load data into Table  
                    using (SqlBulkCopy BC = new SqlBulkCopy(myADONETConnection))
                    {
                        BC.DestinationTableName = "dbo." + TableName;
                        foreach (var column in dt1.Columns)
                            BC.ColumnMappings.Add(column.ToString(), column.ToString());
                        BC.WriteToServer(dt1);
                    }
                }
                cnn.Close();

            }

Save and run the package. If you have the correct paths and variable names, you should see a successful execution of the package.

If you check the table, you should see the data about your files and worksheets.

That's it. modify the schema to match your data.