How to Load Multiple Excel Files with Multiple Sheets to Single SQL Server Table by using SSIS Package - SSIS Tutorial

Scenario: Link to Script

You are working at ETL developer / SSIS Developer in Software development company. You got this requirement in which multiple Excel files with multiple sheets are dropped in a source folder and you have to load them to single SQL Server Table.Following situations can happen

1) You Might get only single Excel file with single Sheet
2) You might get more than one Excel file with single Sheet per File
3) You might get multiple Excel files and number of sheets can be different on each file.
4) Sheets names can be same on all Excel files or different
5) Number of columns can be exact same like your definition table or can vary on each sheet.

Keep all of the above situations in mind, you need to developer an SSIS Package that should be able to load data from single/multiple Files with Single/Multiple sheets for matching columns to SQL Server Table.

Let's assume that we have table name dbo.Customer in TechBrothersIT database with below definition.

CREATE TABLE dbo.Customer (
    id INT
    ,name VARCHAR(50)
    ,dob DATE
    )

Here are some sample files with multiple sheets.
Excel Sample Files to Load to SQL Server Table by using SSIS Package

Customer_TechBrothersIT1.xlsx has two sheets as shown below.
How to load multiple Excel Files with Multiple Sheets to SQL Server Table by Script Task


Dynamic Excel Loading to SQL Server Table by using SSIS Package Script Task


Also Customer_TechBrothersIT2.xlsx has two sheets as can be seen below.
How to Load Excel Dynamically to SQL Server Table by using C# in Script Task in SSIS 


Solution:

We are going to use Script Task in SSIS Package to load multiple Excel files with Multiple Sheets. We will be able to load the data for matching columns from those sheets. If columns does not match with our SQL Server Table name, we will ignore that column data.


Step 1: 
Create your SSIS Package and then create below variables
FolderPath:  Provide the folder path where Excel files will be dropped.
TableName: Provide the table name in which you would like to load sheet data from each File.
SchemaName: Provide the Schema of Table.


 Create Variables in SSIS Package to use in Script Task for Dynamic Excel File Loading


Step2: 
Create ADO.NET Connection Manager as shown below in your SSIS Package.You have to provide SQL Server Instance Name and Database. I have renamed connection to DBConn as can be seen below.
Create ADO.NET Connection to use in Script Task to load data to SQL Server Table - SSIS Package


Step 3: Map variables in Script Task
Bring Script task on Control Flow Task in SSIS Package, Open Script Task and Map variables as shown below.
Map the Variables in Script Task to load Excel files with multiple Sheets dynamically to SQL Server Table


Step 4: 
Click on Edit Script Button and then copy below code to your Script Task Editor.

Under #Region NameSpaces, paste below namespaces.

using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;


Got to public void Main()
{
and paste below code.

            String FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();
            String TableName = Dts.Variables["User::TableName"].Value.ToString();
            String SchemaName = Dts.Variables["User::SchemaName"].Value.ToString();
            var directory = new DirectoryInfo(FolderPath);
            FileInfo[] files = directory.GetFiles();

            //Declare and initilize variables
            string fileFullPath = "";


           SqlConnection myADONETConnection = new SqlConnection();
           myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);

            //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\"";
                OleDbConnection cnn = new OleDbConnection(ConStr);

                //Get Sheet Name
                cnn.Open();
                DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string sheetname;
                sheetname = "";
                //Get Row Count for each sheet
                foreach (DataRow drSheet in dtSheet.Rows)
                {
                    if (drSheet["TABLE_NAME"].ToString().Contains("$"))
                    {
                        sheetname = drSheet["TABLE_NAME"].ToString();

      //Load the DataTable with Sheet Data so we can get the column header
     OleDbCommand oconn = new OleDbCommand("select top 1 * from [" + sheetname + "]", cnn);
                        OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
                        DataTable dt = new DataTable();
                        adp.Fill(dt);
                        cnn.Close();

              //Prepare Header columns list so we can run against Database to get matching               //columns for a table.
                        string ExcelHeaderColumn = "";
                        string SQLQueryToGetMatchingColumn = "";
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            if (i != dt.Columns.Count - 1)
                           ExcelHeaderColumn += "'" + dt.Columns[i].ColumnName + "'" + ",";
                            else
                              ExcelHeaderColumn += "'" + dt.Columns[i].ColumnName + "'";
                        }

             SQLQueryToGetMatchingColumn = "select STUFF((Select  ',['+Column_Name+']' from Information_schema.Columns where Table_Name='" +
                            TableName + "' and Table_SChema='" + SchemaName + "'" +
                            "and Column_Name in (" + @ExcelHeaderColumn + ") for xml path('')),1,1,'') AS ColumnList";

                       // MessageBox.Show(SQLQueryToGetMatchingColumn);
                       // MessageBox.Show(ExcelHeaderColumn);

                     
                        //Get Matching Column List from SQL Server
                        string SQLColumnList = "";
                        SqlCommand cmd = myADONETConnection.CreateCommand();
                        cmd.CommandText = SQLQueryToGetMatchingColumn;
                        SQLColumnList = (string)cmd.ExecuteScalar();

                       // MessageBox.Show(" Matching Columns: " + SQLColumnList);


                        //Use Actual Matching Columns to get data from Excel Sheet
                        OleDbConnection cnn1 = new OleDbConnection(ConStr);
                        cnn1.Open();
                        OleDbCommand oconn1 = new OleDbCommand("select " + SQLColumnList + " from [" + sheetname + "]", cnn1);
                        OleDbDataAdapter adp1 = new OleDbDataAdapter(oconn1);
                        DataTable dt1 = new DataTable();
                        adp1.Fill(dt1);
                        cnn1.Close();


                        //Load Data from DataTable to SQL Server Table.
                        using (SqlBulkCopy BC = new SqlBulkCopy(myADONETConnection))
                        {
                            BC.DestinationTableName = SchemaName + "." + TableName;
                            foreach (var column in dt1.Columns)
                            BC.ColumnMappings.Add(column.ToString(), column.ToString());
                            BC.WriteToServer(dt1);
                        }

                    }
                }
            }


Note: 
I noticed that the code is split to next line sometime.After pasting in Script Task Editor please fix it. Sorry :(

Step 5:
Hit Save button in Script Task Editor and then close it. You are all done. Go ahead and run your SSIS Package. Here are records from a table in my case which are loaded from two excel files with 2 sheets per file.
Load Excel Files Dynamically to SQL Server Table by using SSIS Package with Script Task 




Sub Items Covered in above article:

How to load Data from Excel Files to SQL Server Table by using C#
How to Read Sheet Name from Excel File in SSIS Package
How to read Excel File Name in SSIS Package
How to Check matching Header Column Names to SQL Server Table by using C sharp
How to use BulkCopy to load data from DataTable to SQL Server Table
How to Map Columns Dynamically in BulkCopy C Sharp



Check out our other posts/videos for Dynamic Excel Source and Destination
  1. How to Load Data from Excel Files when Number of Columns can decrease or order is changed in Excel Sheet
  2. How to Load Only Matching Column Data to SQL Server Table from Multiple Excel Files (Single Sheet per file) Dynamically in SSIS Package
  3. How to Load Excel File Names with Sheet Names ,Row Count,Last Modified Date, File Size in SQL Server Table
  4. How to Load Multiple Excel Files with Multiple Sheets to Single SQL Server Table by using SSIS Package
  5. How to Load Matching Sheets from Excel to Table and Log Not Matching Sheets Information in SQL Server Table
  6. How to create Table for each sheet in Excel Files and load data to it dynamically in SSIS Package
  7. How to Create Table per Excel File and Load all Sheets Data Dynamically in SSIS Package by using Script Task 
  8. How to create CSV file per Excel File and Load All Sheets from Excel File to it in SSIS Package
  9. How to Create CSV File for Each Excel Sheet from Excel Files in SSIS Package
  10. How to Load Excel File Name and Sheet Name with Data to SQL Server in SSIS Package
  11. How to Import data from Multiple Excel Sheets with a pattern of sheet names from Multiple Excel File in SSIS Package
  12. How to import Data from Excel Files for specific Sheet Name to SQL Server Table in SSIS Package
  13. Load Data To Tables according to Excel Sheet Names from Excel Files dynamically in SSIS Package
  14. How to Load Excel Files with Single/ Multiple Sheets to SQL Server Tables according to Excel File Name Dynamically
  15. How to Read Excel Sheet Data after Skipping Rows in SSIS Package by using Script Task 
  16. How to read data from Excel Sheet and Load to Multiple Tables by using Script Task in SSIS Package
  17. How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package
  18. How to create Excel File Dynamically for Stored Procedure Results in SSIS Package by using Script Task
  19. How to Export SQL Server Tables from Database to Excel File Dynamically in SSIS Package by using Script Task
  20. How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task
  21. How to Load All CSV Files to Excel Sheets ( Sheet Per CSV) in single Excel File in SSIS Package
  22. How to Load All CSV Files to Single Excel Sheet with File Names in an Excel File Dynamically in SSIS Package
  23. How to Create Sample Excel file with Sheet from each table with Top 1000 Rows per sheet in SSIS Package
  24. How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package