How to create Table for each sheet in Excel Files and load data to it dynamically in SSIS Package - SSIS Tutorial

Scenario: Script to download

You have tons of Excel files sitting in your source folder. Some of the Excel file has single sheet and some of them have multiple sheets. You got this requirement to create table for each of the sheets and load the data. As the tables are going to be dropped and created on each execution in staging database, you are going to use NVARCHAR(4000) for all the columns in your tables.

Once the data is loaded to staging database, you will have another process to read and convert the data to correct data types and load to your final destination tables.

The naming  convention you will be using for the Table will be FileName_SheetName.

here are my excel files with multiple sheets.
Excel files with multiple sheets- How to create new table for each sheet and load data in SSIS

File Customer_TechBrothersIT1.xlsx has two sheets MySheet and Sheet1


Customer_TechBrothersIT2.xlsx has also two sheets. 4 Tables should be created for these two files and 2 sheets.

Solution:

We are going to use the Script Task in SSIS To handle this situation.

Step 1: 
Go ahead and create the variable called  FolderPath, so we can change the value for this variable anytime we need to by using SSIS Configuration.
Create variable in SSIS Package to dynamic creating Table and load data from Excel Sheet


Step 2:
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.
How to use ADO.Net connection in Script Task in SSIS Package for Excel Dynamic Loading


Step 3:
Bring the Script Task to Control Flow Pane and open Script Task and map the variable as shown below.

Map variable in Script Task for Dynamic Excel Loading to SQL server Tables in SSIS Package



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();
            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)
            {
                    string filename = "";
                    fileFullPath = FolderPath+"\\"+file.Name;
                    filename = file.Name.Replace(".xlsx","");
                    MessageBox.Show(fileFullPath);
               
                //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=1\"";
                    OleDbConnection cnn = new OleDbConnection(ConStr);

                
                //Get Sheet Name
                   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();
                     //Display Sheet Name , you can comment it out
                     MessageBox.Show(sheetname);

                     //Load the DataTable with Sheet Data
           OleDbCommand oconn = new OleDbCommand("select * from [" + sheetname + "]", cnn);
                     //cnn.Open();
                     OleDbDataAdapter adp = new OleDbDataAdapter(oconn);
                     DataTable dt = new DataTable();
                     adp.Fill(dt);

                    //drop $from sheet name
                     sheetname = sheetname.Replace("$", "");
                    
                    // Generate Create Table Script by using Header Column,It will drop the table if Exists and Recreate                  
                     string tableDDL = "";
                     tableDDL += "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = ";
                  tableDDL +="OBJECT_ID(N'[dbo].[" + filename + "_"+sheetname+"]') AND type in (N'U'))";
                     tableDDL += "Drop Table [dbo].[" + filename + "_"+sheetname+"]";
                     tableDDL += "Create table [" + filename + "_"+sheetname+"]";
                     tableDDL += "(";
                     for (int i = 0; i < dt.Columns.Count; i++)
                     {
                         if (i != dt.Columns.Count - 1)
                 tableDDL += "[" + dt.Columns[i].ColumnName + "] " + "NVarchar(max)" + ",";
                         else
                      tableDDL += "[" + dt.Columns[i].ColumnName + "] " + "NVarchar(max)";
                     }
                     tableDDL += ")";


                     //use ADO.NET connection to Create Table from above Definition
                     SqlConnection myADONETConnection = new SqlConnection();
                     myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);
                     //you can comment the messagebox, it is for debugging
                     MessageBox.Show(tableDDL.ToString());
                     SqlCommand myCommand = new SqlCommand(tableDDL, myADONETConnection);
                     myCommand.ExecuteNonQuery();
                     //Comment this message, it is for debugging
                     MessageBox.Show("TABLE IS CREATED");


                     //Load the data from DataTable to SQL Server Table.
                     SqlBulkCopy blk = new SqlBulkCopy(myADONETConnection);
                     blk.DestinationTableName = "[" + filename + "_"+sheetname+"]";
                     blk.WriteToServer(dt);
                } 
            }
} 

Step 5:
Save the script in Script Task Editor and close the windows. Run the SSIS Package. It should read the excel files with sheets and create table for each of the sheet and load the data.


New Table created for each Excel sheet and data loaded to it from Excel by using Scrpt Task in SSIS

Let's take a look on data loaded to these Tables from Excel sheets

Table created for Excel Sheet and Data Loaded to it by using Script Task in SSIS Package

2nd Sheet data loaded after creating new table by using Script Task in SSIS Package



After loading all these files to different tables, you might want to archive(move) them to another folder, so when you run the package next time, it don't process them again. You can move them in script task as well. If you like to use File System task , below is the link

Archive Files And Add Timestamp To Each File Name [How To Use File System Task]







Things we learnt in this post
How to use variable in SSIS Package
How to use ADO.NET Connection manager in Script Task in SSIS Package
How to drop and create new table in SQL Server Database from Script Task by using C#
How to load data from Excel Sheet to SQL Server Table by using C# in Script Task in  SSIS Package




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