How to Create Table per Excel File and Load all Sheets Data Dynamically in SSIS Package by using Script Task - SSIS Tutorial

Scenario: Link to Script

Let's consider a situation when you receive single or multiple excel files in source(input) Folder. Each excel file can have single or multiple sheets. But all the sheets have the same columns. We need to create a new table if does not exists for each file and load the data to it. If table already exists we will be loading data from all the sheets to that table according to the name of excel file.

Here are our sample files.

How to create Table for each Excel file and load data from all sheets in SSIS Package

Customer_TechBrothersIT1.xlsx has two sheets in our case with columns Id, name and dob.

Create Table Dynamically for each Excel File in SSIS Package - Script Task C#

Customer_TechBrothersIT2.xlsx has two sheets with columns id and name.

How to load Multiple Sheets to SQL Server Table from Excel File in SSIS Package

Solution:

We are going to use Script Task in SSIS Package to create table for each Excel file and load multiple sheets to it. Here are the steps

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

                                                            
                    // Create Table if does not exists                
                     string tableDDL = "";
                 tableDDL += "IF Not EXISTS (SELECT * FROM sys.objects WHERE object_id = ";
                 tableDDL +="OBJECT_ID(N'[dbo].[" + filename +"]') AND type in (N'U'))";
                 tableDDL += "Create table [" + filename + "]";
                 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 +"]";
                     blk.WriteToServer(dt);
                } 
            }

}

Step 5:
Save the script and Close the Script Task Editor. Run your Package. It should create two tables and load the data from 4 sheets if you have sample files as given above.

Create SQL Table Dynamically from Excel file and load data in SSIS Package - Script Task C#

Lets run query on created tables and take a look if data is loaded from Excel files correctly.

Table created form Customer_TechBrothersIT.xlsx Excel Sheet and Data Loaded from Sheets.

Data loaded to Table from Customer_TechBrothersIT2 Excel File in SSIS Package -Script Task c#

Items we learnt in this post
How to create variable in SSIS Package for folder path and use in Script Task
How to use ADO.NET Connection in Script Task
How to create Table from Script Task in SSIS Package
How to create table by using Excel file name in SSIS Package
How to load the data to SQL server Table from Datatable by using C#
How to load multiple sheets data to Single SQL Server Table
How to loop through Excel files in Script Task and load to matching Table or create new table if not exists