How to read data from Excel Sheet and Load to Multiple Tables by using Script Task in SSIS Package - SSIS Tutorial

Scenario: Download Script

You are getting single/Multiple excel files in source folder. The excel file can have single sheet or can have multiple sheets. Each sheet in Excel follow a pattern ( template). On each sheet we have two tables. In my example I have Customer Table and Product Table as shown below.

We need to read the data for these tables and load to SQL Server Tables. Pay close attention to row number from which the tables start, the column start and end.

How to load multiple table data from Excel Sheet to SQL Server Tables in SSIS Package

Take a detail look on sample excel sheet. The same way you need to have information for your excel sheet. The data rows can decrease or increase on each sheet but structure should not be shifted up or down.

Our SSIS Pakcage will be able to load multiple excel files with single or multiple sheets. Once again, keep it mind the header rows should be fixed for each table and it should be always on same column numbers.


Solution:

We are going to use Script Task in SSIS Package to load multiple tables from Excel sheet to SQL Server Tables. I need two tables as I have to load Customer and Product Tables from Excel Sheet.

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

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

You will be creating tables according to your data in excel sheet.

Step 1: Create an SSIS Package and Create Variables to make SSIS Package Dynamic
Once you have created an SSIS Package by using SSDT ( SQL Server Data Tools), create below variables

You will be creating set of variables for each table. As I have only Customer and Product Table, I have created two sets of variables. If you have more than two tables, you will be create a set of variables for third table and so on.

CustomerSchemaName: Schema name for Customer Table
CustomerTableEndingColumn : Provide the last column where Customer Table data ends
CustomerTableName : Provide the name of Customer Table, exactly same what you have in Database
CustomerTableStartColumn: Provide the name of the column from which Customer data start
CustomerTableStartReadingFromRow : Provide the Row number from which the Customer Table Start
Folder Path:  Provide the folder in which our excel files will be placed or dropped.


I am going to type all variables for Product. But they are shown below. Once again, for every new table we have to create same set of variables as we did for Customer.

Create Variables in SSIS To load multiple Data Regions from Excel Sheet to SQL Server Tables in SSIS Package by using Script Task

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 different Data regions from Excel Sheet to SQL Server Table


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.
How to use variables in Script Task to Read Data from Excel sheet by skipping rows and columns in SSIS Package


Step 4: Add Script to Script task Editor in SSIS To load different data regions from excel sheet to sQL sever Tables
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();
            //Declare Set of variables for each type of table ( Customer here)
            string CustomerTableName = Dts.Variables["User::CustomerTableName"].Value.ToString();
            string CustomerSchemaName = Dts.Variables["User::CustomerSchemaName"].Value.ToString();
            string CustomerTableStartingColumn = Dts.Variables["User::CustomerTableStartingColumn"].Value.ToString();
            string CustomerTableEndingColumn = Dts.Variables["User::CustomerTableEndingColumn"].Value.ToString();
            string CustomerTableStartReadingFromRow = Dts.Variables["User::CustomerTableStartReadingFromRow"].Value.ToString();


            //Declare Set of variables for each type of table (Product here)
            string ProductTableName = Dts.Variables["User::ProductTableName"].Value.ToString();
            string ProductSchemaName = Dts.Variables["User::ProductSchemaName"].Value.ToString();
            string ProductTableStartingColumn = Dts.Variables["User::ProductTableStartingColumn"].Value.ToString();
            string ProductTableEndingColumn = Dts.Variables["User::ProductTableEndingColumn"].Value.ToString();
            string ProductTableStartReadingFromRow = Dts.Variables["User::ProductTableStartReadingFromRow"].Value.ToString();
            
            //if you will have new table, copy above 5 lines and paste and change according to Table Name

            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);
            
                //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 for Customer
                        OleDbCommand Customer_conn = new OleDbCommand("select top 1 * from [" + sheetname + CustomerTableStartingColumn + CustomerTableStartReadingFromRow + ":" + CustomerTableEndingColumn + "]", cnn);
                        OleDbDataAdapter Customer_adp = new OleDbDataAdapter(Customer_conn);
                        DataTable Customer_dt = new DataTable();
                        Customer_adp.Fill(Customer_dt);
                        cnn.Close();

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

                        CustomerSQLQueryToGetMatchingColumn = "select STUFF((Select  ',['+Column_Name+']' from Information_schema.Columns where Table_Name='" +
                            CustomerTableName + "' and Table_SChema='" + CustomerSchemaName + "'" +
                            "and Column_Name in (" + @CustomerExcelHeaderColumn + ") 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 CustomerSQLColumnList = "";
                        SqlCommand Customer_cmd = myADONETConnection.CreateCommand();
                        Customer_cmd.CommandText = CustomerSQLQueryToGetMatchingColumn;
                        CustomerSQLColumnList = (string)Customer_cmd.ExecuteScalar();

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


                        //Use Actual Matching Columns to get data from Excel Sheet
                        OleDbConnection Customer_cnn1 = new OleDbConnection(ConStr);
                        Customer_cnn1.Open();
                        OleDbCommand Customer_conn1 = new OleDbCommand("select " + CustomerSQLColumnList + " from [" + sheetname + CustomerTableStartingColumn + CustomerTableStartReadingFromRow + ":" + CustomerTableEndingColumn + "]", Customer_cnn1);
                        OleDbDataAdapter Customer_adp1 = new OleDbDataAdapter(Customer_conn1);
                        DataTable Customer_dt1 = new DataTable();
                        Customer_adp1.Fill(Customer_dt1);
                        Customer_cnn1.Close();


                        //Delete a row from database if all values are null

                        int CustomercolumnCount = Customer_dt1.Columns.Count;

                        for (int i = Customer_dt1.Rows.Count - 1; i >= 0; i--)
                        {
                            bool allNull = true;
                            for (int j = 0; j < CustomercolumnCount; j++)
                            {
                                if (Customer_dt1.Rows[i][j] != DBNull.Value)
                                {
                                    allNull = false;
                                }
                            }
                            if (allNull)
                            {
                                Customer_dt1.Rows[i].Delete();
                            }
                        }
                        Customer_dt1.AcceptChanges();


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

                        //Repeate above code for Product Table. If you have more table. Copy above code, replace Customer with your Table name and paste below
                        //Load the DataTable with Sheet Data so we can get the column header for Product
                        OleDbCommand Product_conn = new OleDbCommand("select top 1 * from [" + sheetname + ProductTableStartingColumn + ProductTableStartReadingFromRow + ":" + ProductTableEndingColumn + "]", cnn);
                        OleDbDataAdapter Product_adp = new OleDbDataAdapter(Product_conn);
                        DataTable Product_dt = new DataTable();
                        Product_adp.Fill(Product_dt);
                        cnn.Close();

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

                        ProductSQLQueryToGetMatchingColumn = "select STUFF((Select  ',['+Column_Name+']' from Information_schema.Columns where Table_Name='" +
                            ProductTableName + "' and Table_SChema='" + ProductSchemaName + "'" +
                            "and Column_Name in (" + @ProductExcelHeaderColumn + ") for xml path('')),1,1,'') AS ColumnList";

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

                   

                        //Get Matching Column List from SQL Server
                        string ProductSQLColumnList = "";
                        SqlCommand Product_cmd = myADONETConnection.CreateCommand();
                        Product_cmd.CommandText = ProductSQLQueryToGetMatchingColumn;
                        ProductSQLColumnList = (string)Product_cmd.ExecuteScalar();

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


                        //Use Actual Matching Columns to get data from Excel Sheet
                        OleDbConnection Product_cnn1 = new OleDbConnection(ConStr);
                        Product_cnn1.Open();
                        OleDbCommand Product_conn1 = new OleDbCommand("select " + ProductSQLColumnList + " from [" + sheetname + ProductTableStartingColumn + ProductTableStartReadingFromRow + ":" + ProductTableEndingColumn + "]", Product_cnn1);
                        OleDbDataAdapter Product_adp1 = new OleDbDataAdapter(Product_conn1);
                        DataTable Product_dt1 = new DataTable();
                        Product_adp1.Fill(Product_dt1);
                        Product_cnn1.Close();



                        //Delete a row from database if all values are null

                        int ProductcolumnCount = Product_dt1.Columns.Count;
                        for (int i = Product_dt1.Rows.Count - 1; i >= 0; i--)
                        {
                            bool allNull = true;
                            for (int j = 0; j < ProductcolumnCount; j++)
                            {
                                if (Product_dt1.Rows[i][j] != DBNull.Value)
                                {
                                    allNull = false;
                                }
                            }
                            if (allNull)
                            {
                                Product_dt1.Rows[i].Delete();
                            }
                        }
                        Product_dt1.AcceptChanges();

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

                    }
                }
                
            }


Step 5: Save the Script and Run the SSIS Package to load data from different regions from Excel sheet to SQL Server Tables

Save the script in Script Task Editor and close the window. Execute the SSIS Package and it should load SQL Server Tables from different Data regions from an Excel Sheet. 

If all the values in a row are Null, that row will be removed. 
I ran the package for above sample file and here are my results.

Load Data from Excel Sheet to different Tables after skipping Rows and Columns in SSIS Package by using Script Task - C# Scripting language


No comments:

Post a Comment