How to Export All tables of a database to Excel Files with Datetime in SSIS Package - SSIS Tutorial

Scenario: Download Script

Long time back, I wrote a post How to Export all SQL Server Tables to CSV/Text Files. You can check here. In this post we are going to learn How to create Excel file per table for all the tables from a database. 


Solution:

We are going to use Script Task in SSIS Package to export all the tables from SQL Server database to Excel Files. 

Step 1: 
Open SSDT ( SQL Server Data Tools), create new SSIS Package. Create a variable FolderPath where you would like to export all the tables from a database to excel files.

Create variable in SSIS Package to create excel file per table from a database- 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 Excel Files. This ADO.Net connection should be pointing to Database from which we would like to export tables to Excel Files.

Create ADO.NET Connection to Database from which we would like to export all the tables to Excel Files in SSIS


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 SSIS Package Variables to Script Task to create excel files for all the tables from a database and export data


Step 4: Add Script to Script task Editor in SSIS Package to Export All tables from SQL server Database to Excel Files with datetime.
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 FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();
                
               //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 list of Tables with Schema from Database
                string query = "SELECT Schema_name(schema_id) AS SchemaName,name AS TableName FROM   sys.tables WHERE  is_ms_shipped = 0";

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

                //Loop through datatable(dt) that has schema and table names

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


                    string ExcelFileName = "";
                    ExcelFileName = SchemaName+"_"+TableName + "_" + 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;\"";

                    //Load Data into DataTable from SQL ServerTable
                  
                    string queryString =
                      "SELECT * from " + SchemaName + "." + TableName;
                    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 [" + SchemaName+"_"+TableName + "] (" + 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 [" + SchemaName+"_"+TableName + "] (" + 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 script and Run SSIS Package to Export all tables from a database to Excel Files
Save the script in Script Task Editor and then close the window. Run your SSIS Package, it should export all tables from SQL Server database to Excel Files. Each Table will be export to new Excel file with datetime.

I execute package for my database and it created below excel files as shown below.

 How to export all tables from a database to Excels files in SSIS Package - SSIS Tutorial


Check the video Demo how to export all tables from sql server database to Excel Files



Check out our other posts/ videos on Excel Source and Destinations (Script Task- Dynamic)
  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
  25. How to split large table data into multiple Excel Sheets on Single Excel File by using SSIS Package
  26. How to Export All tables of a database to Excel Files with Date-time in SSIS Package
  27. How to read Cell Value from Excel by using Script Task in SSIS Package

3 comments:

  1. Hello Tech Brother,
    I tried your C# script. It works great from my PC with large amount of records. But after deploying it to SQL SERVER 2017 and when I execute it manually from Integration Service Catalogs, it works only with less than around 800 records. If exporting more data (for example, more than 1400 record) into Excel, I will have "Unexpected Termination". Sometimes data loaded and sometimes data not loaded with "Unexpected Termination". I installed Microsoft Access Database Engine 2016 (AccessDatabaseEngine_X64.exe) on both my pc and SQL server. Could you help me?
    Thanks,
    Chris

    ReplyDelete
  2. System.InvalidOperationException: ExecuteReader: Connection property has not been initialized.
    at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader()
    at ST_1f38c5151ef6402f9ec3649f7bee363d.ScriptMain.Main()

    ReplyDelete