How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package - SSIS Tutorial

Scenario: Download Script

You are working as ETL developer or an SSIS developer. You have a table let's say dbo.TotalSale. Once of the column in this table is Region. You would like to create an Excel file with Multiple Sheets, each for Region Value. 

If there are three distinct Regions values in Region Column, it should create an Excel file with three excel sheets. 

So if tomorrow, the Region decreases or increases, the sheets should be created according to distinct values.

In your scenario, you can chose any column that you would like to use for dividing the table data to multiple sheets.

How to create multiple Excel sheets from single SQL Server Table in SSIS Package - SSIS Tutorial


Solution:

We are going to use Script Task in SSIS Package to create multiple Excel Sheets for single Table data depending upon the distinct value from a column. 

Step 1: Create new SSIS Package and create Variables 
Open your SSDT ( SQL Server Data Tools) and create new SSIS Package. Once done, create below variables as shown.

ColumnNameForGrouping : Provide the column you would like to use for creating sheet data
ExcelFileName : Provide the excel file name you would like to create
FolderPath: Provide the Path where you would like to create excel files
TableName: Provide the table name with schema from which you like to get data

Create variables in SSIS Package to create Multiple Excel Sheets from SQL Server Table - SSIS Tutorial

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 get tables data to export to sheets in Excel File. This ADO.Net connection should be pointing to Database that we want to export to an Excel File.

Create ADO.NET Connection in SSIS Package to use in Script Task to Export All Tables from Database to Excel File with Top X Rows

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.
Add variables to Script Task to create multiple excel sheets from sql server table in SSIS Package


Step 4: Add Script to Script task Editor in SSIS Package to Export Data from single SQL Server Table to Multiple Excel Sheets in Excel File
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 datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            try
            {

                //Declare Variables
                string ExcelFileName = Dts.Variables["User::ExcelFileName"].Value.ToString();
                string FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();
                string TableName = Dts.Variables["User::TableName"].Value.ToString();
                string ColumnNameForGrouping = Dts.Variables["User::ColumnNameForGrouping"].Value.ToString();
                ExcelFileName = ExcelFileName + "_" + datetime;


                OleDbConnection Excel_OLE_Con = new OleDbConnection();
                OleDbCommand Excel_OLE_Cmd = new OleDbCommand();

                //Construct ConnectionString for Excel
                string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FolderPath + ExcelFileName
                    + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";

                //drop Excel file if exists
                File.Delete(FolderPath + "\\" + ExcelFileName + ".xlsx");

                //USE ADO.NET Connection from SSIS Package to get data from table
                SqlConnection myADONETConnection = new SqlConnection();
                myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);


                //Read distinct Group Values for each Excel Sheet
                string query = "Select distinct "+ColumnNameForGrouping+" from "+TableName;

                //MessageBox.Show(query.ToString());
                SqlCommand cmd = new SqlCommand(query, myADONETConnection);
                //myADONETConnection.Open();
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                myADONETConnection.Close();

                //Loop through values for ColumnNameForGroup

                foreach (DataRow dt_row in dt.Rows)
                {
                    string ColumnValue = "";
                    object[] array = dt_row.ItemArray;
                    ColumnValue = array[0].ToString();
                   

                    //Load Data into DataTable from SQL ServerTable
                     string queryString =
                      "SELECT * from " + TableName+" Where "+ColumnNameForGrouping+"='"+ColumnValue+"'";
                    SqlDataAdapter adapter = new SqlDataAdapter(queryString, myADONETConnection);
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);


                    //Get Header Columns
                    string TableColumns = "";

                    // Get the Column List from Data Table so can create Excel Sheet with Header
                    foreach (DataTable table in ds.Tables)
                    {
                        foreach (DataColumn column in table.Columns)
                        {
                            TableColumns += column + "],[";
                        }
                    }

                    // Replace most right comma from Columnlist
                    TableColumns =("["+ TableColumns.Replace(",", " Text,").TrimEnd(','));
                    TableColumns = TableColumns.Remove(TableColumns.Length - 2);
                    //MessageBox.Show(TableColumns);


                    //Use OLE DB Connection and Create Excel Sheet
                    Excel_OLE_Con.ConnectionString = connstring;
                    Excel_OLE_Con.Open();
                    Excel_OLE_Cmd.Connection = Excel_OLE_Con;
                    Excel_OLE_Cmd.CommandText = "Create table [" +ColumnValue + "] (" + TableColumns + ")";
                    Excel_OLE_Cmd.ExecuteNonQuery();


                    //Write Data to Excel Sheet from DataTable dynamically
                    foreach (DataTable table in ds.Tables)
                    {
                        String sqlCommandInsert = "";
                        String sqlCommandValue = "";
                        foreach (DataColumn dataColumn in table.Columns)
                        {
                            sqlCommandValue += dataColumn + "],[";
                        }

                        sqlCommandValue="["+sqlCommandValue.TrimEnd(',') ;
                        sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2);
                        sqlCommandInsert = "INSERT into [" + ColumnValue + "] (" + sqlCommandValue +") VALUES(";

                        int columnCount = table.Columns.Count;
                        foreach (DataRow row in table.Rows)
                        {
                            string columnvalues = "";
                            for (int i = 0; i < columnCount; i++)
                            {
                                int index = table.Rows.IndexOf(row);
                                columnvalues += "'" + table.Rows[index].ItemArray[i] + "',";

                            }
                            columnvalues = columnvalues.TrimEnd(',');
                            var command = sqlCommandInsert + columnvalues + ")";
                            Excel_OLE_Cmd.CommandText = command;
                            Excel_OLE_Cmd.ExecuteNonQuery();
                        }

                    }
                    Excel_OLE_Con.Close();
                }
                Dts.TaskResult = (int)ScriptResults.Success;
            }

            catch (Exception exception)
            {
               
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(Dts.Variables["User::FolderPath"].Value.ToString() + "\\" +
                    Dts.Variables["User::ExcelFileName"].Value.ToString() + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;
                    
                }

               }


Step 5: Save the script and Run SSIS Package to create Multiple Sheets from single SQL Server Table
Hit save button and then close the script task editor window. Run your SSIS Package and it should create an excel file with multiple sheets depending upon unique regions from region column.

I ran my SSIS Package for above table, and here is my Excel file with multiple sheets.
How to create multiple excel sheets from single sql server table in SSIS Package by using Script Task




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

14 comments:

  1. I'm trying to create multiple excel files instead of multiple excel sheets.
    Can you please help.

    ReplyDelete
    Replies
    1. string datetime = DateTime.Now.ToString("yyyyMMdd");
      try
      {

      //Declare Variables
      string FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();
      string TableName = Dts.Variables["User::TableName"].Value.ToString();
      string ColumnNameForGrouping = Dts.Variables["User::ColumnNameForGrouping"].Value.ToString();
      string ExcelFileName = "";

      OleDbConnection Excel_OLE_Con = new OleDbConnection();
      OleDbCommand Excel_OLE_Cmd = new OleDbCommand();

      //USE ADO.NET Connection from SSIS Package to get data from table
      SqlConnection myADONETConnection = new SqlConnection();
      myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);


      //Read distinct Group Values for each Excel Sheet
      string query = "Select distinct " + ColumnNameForGrouping + " from " + TableName; //+ " where Key_Month = '201907'";

      //MessageBox.Show(query.ToString());
      SqlCommand cmd = new SqlCommand(query, myADONETConnection);
      //myADONETConnection.Open();
      DataTable dt = new DataTable();
      dt.Load(cmd.ExecuteReader());
      myADONETConnection.Close();

      //Loop through values for ColumnNameForGroup

      foreach (DataRow dt_row in dt.Rows)
      {
      string CountryValue = "";
      object[] array = dt_row.ItemArray;
      CountryValue = array[0].ToString();
      ExcelFileName = CountryValue + "_" + datetime;

      //Construct ConnectionString for Excel
      string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FolderPath + ExcelFileName
      + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";

      //drop Excel file if exists
      File.Delete(FolderPath + "\\" + ExcelFileName + ".xlsx");


      //Load Data into DataTable from SQL ServerTable
      string queryString =
      "SELECT * from " + TableName + " Where " + ColumnNameForGrouping + "='" + CountryValue + "'";
      SqlDataAdapter adapter = new SqlDataAdapter(queryString, myADONETConnection);
      DataSet ds = new DataSet();
      adapter.Fill(ds);


      //Get Header Columns
      string TableColumns = "";

      // Get the Column List from Data Table so can create Excel Sheet with Header
      foreach (DataTable table in ds.Tables)
      {
      foreach (DataColumn column in table.Columns)
      {
      TableColumns += column + "],[";
      }
      }

      // Replace most right comma from Columnlist
      TableColumns = ("[" + TableColumns.Replace(",", " Text,").TrimEnd(','));
      TableColumns = TableColumns.Remove(TableColumns.Length - 2);
      //MessageBox.Show(TableColumns);

      Delete
    2. //Write Data to Excel Sheet from DataTable dynamically
      foreach (DataTable table in ds.Tables)
      {
      //Use OLE DB Connection and Create Excel Sheet
      Excel_OLE_Con.ConnectionString = connstring;
      Excel_OLE_Con.Open();
      Excel_OLE_Cmd.Connection = Excel_OLE_Con;
      Excel_OLE_Cmd.CommandText = "Create table [" + CountryValue + "] (" + TableColumns + ")";
      Excel_OLE_Cmd.ExecuteNonQuery();

      String sqlCommandInsert = "";
      String sqlCommandValue = "";
      foreach (DataColumn dataColumn in table.Columns)
      {
      sqlCommandValue += dataColumn + "],[";
      }

      sqlCommandValue = "[" + sqlCommandValue.TrimEnd(',');
      sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2);
      sqlCommandInsert = "INSERT into [" + CountryValue + "] (" + sqlCommandValue + ") VALUES(";

      int columnCount = table.Columns.Count;
      foreach (DataRow row in table.Rows)
      {
      string CountryValues = "";
      for (int i = 0; i < columnCount; i++)
      {
      int index = table.Rows.IndexOf(row);
      CountryValues += "'" + table.Rows[index].ItemArray[i] + "',";

      }
      CountryValues = CountryValues.TrimEnd(',');
      var command = sqlCommandInsert + CountryValues + ")";


      Excel_OLE_Cmd.CommandText = command;
      Excel_OLE_Cmd.ExecuteNonQuery();

      }

      Excel_OLE_Con.Close();
      }

      }
      Dts.TaskResult = (int)ScriptResults.Success;
      }

      catch (Exception exception)
      {

      // Create Log File for Errors
      using (StreamWriter sw = File.CreateText(Dts.Variables["User::FolderPath"].Value.ToString() + "\\" +
      datetime + ".log"))
      {
      sw.WriteLine(exception.ToString());
      Dts.TaskResult = (int)ScriptResults.Failure;

      }

      }

      Delete
    3. Hi, I added 2 parts of the code (I couldn't paste the whole code into 1 comment) in order to create several excel files instead of worksheets. You need to remove the parameter ExcelFileName and use this field in the code to assign the dynamic name for your new excels. I used country name as ExcelFileName. Good luck!

      Delete
  2. I was trying to create excel as per your tutorial but I am getting below error.
    I am using SSIS 2012 I am very poor in scripting
    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()

    ReplyDelete
  3. I have got this code to work however it is putting an underscore in front of everysheet name. Also i do I use a template using this code

    ReplyDelete
    Replies
    1. How did you add template to this process? Can you please write steps?

      Delete
    2. facing same issue. using same code. no changes at all. only thing is. my column is of INT datatype based on which grouping is happening. thanks in advance !

      Delete
  4. The code works great. But how can you keep the data type from sql when exporting into excel? my date and int column outputs are showing as text.

    ReplyDelete
  5. The code works great. But how can you keep the data type from SQL when exporting into excel? my date and int column outputs are showing as text.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. you can use below code : for decimal/int = use number or varchar/nvarchar = use text :
      int columnIndex;
      foreach (DataTable table in ds.Tables)
      {
      columnIndex = 0;
      foreach (DataColumn column in table.Columns)
      {
      //TableColumns += column + "],[";

      switch(columnIndex)
      {
      case 0:
      TableColumns += column + "] text,[";
      break;

      default:
      TableColumns += column + "] number,[";
      break;
      }

      columnIndex++;
      }
      }

      Delete
  6. I got it to the end up until running the script run succesfully, but the file is nowhere to be found on the FolderPath. I tried changing file path or giving all the permissions to the folder to check if that was the issue.

    ReplyDelete
  7. you code works perfectly fine. but i'm getting underscore in front of every sheet name. my grouping column is INT datatype. three digit number based on which I am creating multiple sheets. can you help me with removal of underscore? using same code as it is. no change !

    ReplyDelete