How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package - SSIS Tutorial

Scenario: Download Script

You are working as ETL Developer / SSIS Developer in one of the Auto Insurance Company. You got this requirement " Create an SSIS Package that should read the data from SQL server Table and create an Excel file with Datetime and load data from SQL server Table". On each execution the new Excel file should be created. Once problem with requirement is that the SSIS Package should be able to handle new columns dynamically or if columns dropped from Table, it should not fail and simply start creating file with available columns.

Solution:

Long time back, I wrote a post 

Create Excel File Dynamically In SSIS  

that uses  Microsoft.Office.Interop.Excel.dll and we used in script task to create excel file dynamically form SQL server Table.

In this post we are going to use Microsoft.ACE.OLEDB.12.0 provider.


Log File Information : In case your SSIS Package fail. Log file will be created in the same folder where your Excel File will be created. It will have the same datetime like your Excel File.

Step 1: Create Variables to make your SSIS Package Dynamic
Create your SSIS Package in SSDT ( SQL Server Data Tools).  Once SSIS Package is created. Create below variables. 

ExcelFileName : Provide the name of your Excel File that you would like to create
FolderPath: This will save where you would like to create Excel File
SheetName: Provide the Sheet Name you like to have in your Excel File
TableName : Provide the Table Name or View Name with Schema from which you would like to extract data for your excel sheet in Excel file
Create Variable in SSIS Package to generate Excel File Dynamically from SQL server Table or View 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 get data from SQL server Table/View to Load into Excel File Dynamically in SSIS Package




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 generate Excel File Dynamically from SQL Server Table/ View in SSIS Package



Step 4: Add Script to Script task Editor in SSIS To create Excel File with Datetime Dynamically from SQL Server Table or View
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 SheetName = Dts.Variables["User::SheetName"].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);

                //Load Data into DataTable from SQL ServerTable
                // Assumes that connection is a valid SqlConnection object.
                string queryString =
                  "SELECT * from " + 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 " + SheetName + " (" + 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 " + SheetName + "(" + sqlCommandValue.TrimEnd(',') + ") 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 in Script Task Editor and close the window. Run your SSIS Package. It should read the data from table or view you have provided as variable value and it should generate an excel file with sheet name. 
I ran the package for my table and here is the file it generate. You can use this SSIS Package as template. By providing different values to variables, you can create excel file for any table or view.



Create New Excel File Dynamically to Export Data from SQL Server Table or View by using Script task in SSIS Package








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













18 comments:


  1. Thanks , this works well. But i do have some values which has quotes in between the text, like Australian Int'l School. The code fails with the following error for such records. What change can be done to correct this?

    Syntax error (missing operator) in query expression ''Australian Int'l School

    ReplyDelete
  2. Ace4sure is the website that deals in preparation material for the exam for many years. According to my exposure and research, this is the right platform where you can get exact HPE6-A42 Dumps Questions.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Hi I'm also having the issue where the package is throwing the syntax error Syntax error (missing operator) in query expression where there are single quotes in the text. Is there a solution for this? Many thanks

    ReplyDelete
  5. Hi,
    Is it possible to have numeric values displayed as numbers, not as strings in the excel?

    ReplyDelete
  6. facing the below error

    System.Data.OleDb.OleDbException (0x80004005): IErrorInfo.GetDescription failed with E_FAIL(0x80004005)

    ReplyDelete
  7. The structure will permit you to make an Excel article and control a portion of the essential functionalities, for example, getting worksheet data and extricating information from the worksheet given a reach.
    Excel spreadsheet consultant

    ReplyDelete
  8. Thanks for this. Helped me out a lot!

    ReplyDelete
  9. Hi! I'm try start your code, but i have read-wrute error. Can you help me with next error:
    System.Data.OleDb.OleDbException (0x80004005): cannot update. database or object is read-only

    ReplyDelete
  10. I have a problem with data in a sql table that contains the ' character in its name.
    What to correct in the code so that this error is not there?

    ReplyDelete
    Replies
    1. Not easy to fix with table names containing quotes in their name, as strings are used throughout this code. Can you create a view that selects from the table and run the code against the view?

      Delete
    2. the modification below worked for me.
      columnvalues += "'" + table.Rows[index].ItemArray[i].ToString().Replace("'", "''") + "',";
      Good luck!



      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].ToString().Replace("'", "''") + "',";

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

      Delete
  11. I Need write table in existing excel template with out creating new excel sheet . Also table also come with dynamic fileds

    ReplyDelete
  12. Excellent article. However I am facing the below error:
    "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."

    Pretty sure the Microsoft.ACE.OLEDB.12.0 is registered and works for other SSIS packages. I inserted the $ in sheetname too. Still couldnt resolve the above error

    ReplyDelete
  13. Worked great, thanks for posting!

    ReplyDelete
  14. This is very informative and interesting for me. Thank you for such a wonderful article and for sharing. God bless you. We also provide service for Travel Portal System With Dynamic Packaging for more info visit our website.

    ReplyDelete
  15. This Article is Awesome. It’s helped me a lot. Please keep up your good work. We are always with you and Waiting for your new interesting articles. Dynamic Packaging System Software.

    ReplyDelete
  16. Master Excel 2016 Essential Skills is very much organized with simple to follow meetings and examples. One thing you will see straight away is that the book is bigger than numerous other Excel books. placement cells database

    ReplyDelete