Welcome To TechBrothersIT: How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package

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

Script to create TotalSale Table.

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

15 comments:

UnknownFebruary 15, 2019 at 12:52 AM
I'm trying to create multiple excel files instead of multiple excel sheets.
Can you please help.
ReplyDelete
Replies
UnknownFebruary 25, 2019 at 10:32 AM
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
Replies
BrichJune 19, 2019 at 11:14 AM
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
UnknownJanuary 7, 2020 at 10:39 AM
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
UnknownJanuary 7, 2020 at 11:04 AM
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
PakmanJune 12, 2020 at 2:12 PM
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
Replies
rbkMarch 14, 2022 at 2:21 AM
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
Replies
SammysamMay 14, 2024 at 7:42 AM
How do I overcome apostrophe's in email addresses when using this script cause it's throwing errors? I
ReplyDelete
Replies

Add comment

Label

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

Scenario: Download Script

Solution:

15 comments: