How to Import data from Multiple Excel Sheets with a pattern of sheet names from Multiple Excel File in SSIS Package - SSIS Tutorial

Scenario: Download Script

We get single or multiple Excel files in one of the Source Folder. The Excel File can have single or multiple Sheets. We need to load only Sheets to our SQL Server which name matched with our name pattern. 

From below Excel Files, I am only interest to load Excel sheets with contain "Customer" in the name. What will be your approach to load data from Excel Sheets by matching name pattern to SQL Server Table?


Solution:

We are going to use Script Task in SSIS Package to load all the sheets which contain Customer in the name. The package should be dynamic enough so if tomorrow we need to use different pattern to check, we should be able to pass by using configuration and it should load those excel sheets to table.


Create the table dbo.Customer depending upon the columns of our Excel Sheets.

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




Step 1: Create Variable in SSIS to Make your SSIS Package Dynamic
Create below variables in your SSIS Package
FolderPath: In this variable you will save the path from where you would like to read Excel Files
SchemaName: Schema of your Destination Table
TableName: Table in which you want to load the data
SheetNameLike: Provide the Sheet Name you would like to load from each Excel File.

Create variable in SSIS Package to load Excel Sheet with matches Name Pattern Script Task C#


Step 2: Create ADO.NET Connection in SSIS Package to use in Script Task
Create ADO.NET Connection Manager so we can use in Script Task to Load data from Excel Sheets to SQL Server Table.

Create ADO.NET Connection in SSIS Package to use in Script Task to Load Excel Sheet By Name Match

Step3: Add Variables to Script Task to use from SSIS Package
Bring the Script Task on Control Flow Pane in SSIS Package and open by double clicking Check-box in front of variable to add to Script Task.
Use SSIS Variables in Script Task to Load Excel Sheets which match Name Pattern - Excel Dynamic Loading


Step 4: Add Script to Script task Editor in SSIS To load Matching Excel Sheets by Name
Click Edit Button and it will open Script Task Editor.
Under #region Namespaces, I have added below code
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;


Under public void Main() { 
I have added 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();
            string SheetNameToLoad = Dts.Variables["User::SheetNameLike"].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 = "";
                //Only read data from provided SheetNumber
                
                foreach (DataRow drSheet in dtSheet.Rows)
                {
                   
                        if (drSheet["TABLE_NAME"].ToString().Contains("$"))
                        {
                            sheetname = drSheet["TABLE_NAME"].ToString();
                           // MessageBox.Show(sheetname);
                        
                      //Load the Data if Sheet Name contains value of SheetNameLike 
                            if(sheetname.Contains(SheetNameToLoad)==true)
                            {

                          

                            //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);
                            }

                        }
                    }
                   
                }
               
            }


Step 5: Save Script and Execute SSIS Package to Load Excel Sheets which matched with our Sheet Name Pattern 

Save the script in Script Task Editor and close the editor. Run the Package and it should load three sheet data from our sample files as three Excel sheets has "Customer" keyword in sheet name.

You can use this package to load data to any table. You need to change Schema Name, Table name and like term for your excel sheets and it should work just fine.

Import Excel Sheet by name matching in SSIS Package by using Script Task - C# Scripting Language





Things we learnt in this post
How to load Data from Excel Files to SQL Server Table by using C#
How to read data from Specific Sheet by using Sheet Name match with our criteria from Excel File and load to SQL Table
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 save scalar string value from SQL Server Query to Variable in 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