Welcome To TechBrothersIT: How to Load Excel Files with Single/ Multiple Sheets to SQL Server Tables according to Excel File Name Dynamically

Scenario: Download Script

We receive single or multiple files in our source folder daily. These Excel Files can have single or multiple Sheets. We need to load these Excel files to SQL Server Tables. The Excel File Name contains table name. Here are few sample files I created. I tried my best to create files names keeping in mind that you might get file names in different formats.

Most common file formats are FileName_DateTime.xlsx or maybe you always get FileName_FileNumber etc.

How to load Excel Files according to the name to SQL Server Table in SSIS Package Dynamically

Pay attention so file names. I am very much sure you will get your files from one of above format. I extracted the name from file. My table name is exactly like that. I have Customer and Product Tables.

The Excel files can have single or multiple sheets as long as the columns match with table columns, we are good to load them.

I opened couple of files and you can see it has multiple sheets.

How to load Excel file dynamically to SQL server Table by using File Name as Table Name in SSIS Package by using Script Task

Solution:

We are going to use Script Task in SSIS Package to handle this scenario for loading excel data from excel files to SQL Server depending upon the Excel File Name.

Let's create Product and Customer tables after talking a look into our Excel Sheets on each excel file.

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

CREATE TABLE dbo.Product (
    ProductId INT
    ,ProductName VARCHAR(100)
    )

I created the tables with dbo schema. You can change with different schema if you need to. Our SSIS Package is going to have a variable SchemaName so we can provide schema name for our tables.

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/s

Create Variables in SSIS Package To load Excel Data to SQL server Table/s depending upon Excel file name

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 File Data according to the name to SQL Server Tables Dynamically

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 Variables in Script Task in SSIS Package to Load Excel File data Dynamically to SQL Server Tables

Step 4: Add Script to Script task Editor in SSIS To load Excel Data with File Name and Sheet 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 = "";
            String SchemaName = Dts.Variables["User::SchemaName"].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\"";
                    OleDbConnection cnn = new OleDbConnection(ConStr);
            
                //Remove All Numbers and other characters and leave alphabets for name
                    System.Text.RegularExpressions.Regex rgx = new System.Text.RegularExpressions.Regex("[^a-zA-Z]");
                    TableName = rgx.Replace(file.Name, "").Replace("xlsx","");
                //MessageBox.Show(TableName);
                //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();
                     
                     //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);

                     //USE ADO.NET Connection
                     SqlConnection myADONETConnection = new SqlConnection();
                     myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);

                     //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: Run SSIS Package to Load Excel Data to SQL Server table/s according to Excel File Names

Save the script and Close the Script Task Editor. Run your SSIS Package. It should load the data from single or multiple excel sheets to SQL Server table depending upon Excel file name.

I ran my package and it loaded data to Product and Customer Table from above Files.

How to load data from Excel File to SQL Server table according to the name of Excel file 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 load Excel files by name to SQL server Tables in SSIS Package by using Script Task

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

2 comments:

UnknownJune 17, 2019 at 4:53 PM
Hi, I got an error while running the script..wondering if you could advice ...thank you very much...

" at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()"
Michal SimalOctober 7, 2022 at 4:29 AM
Script fail if I have .xlxs file which is not in Information_schema.Columns Table_Name. How I can solve this ? How can I import multiple excel files with different Table_Schema ? great work btw :)

Welcome To TechBrothersIT

Label

How to Load Excel Files with Single/ Multiple Sheets to SQL Server Tables according to Excel File Name Dynamically - SSIS Tutorial

Scenario: Download Script

Solution:

2 comments: