How to create Excel File Dynamically for Stored Procedure Results in SSIS Package by using Script Task - SSIS Tutorial

Scenario: Download Script

You are working as ETL Developer / SSIS Developer and you need to create an SSIS Package that should execute Stored Procedure from SQL Server database and create an excel file for data returned by Stored Procedure. Let's assume that in in scenario, the Stored Procedure does not accept any parameters. 

Often Stored Procedures are created to run bunch of queries to generate final results and load that to Excel file. Your Stored Procedure might be using pivot and it can return different columns on each execution. Our SSIS Package should be able to handle this situation. It should always create an Excel file whatever columns are returned by Stored Procedure.

The excel file should be generate with Datetime on each execution. 

Solution:

If we try to create this SSIS Package with Excel Destination,It is going to be very hard to handle situations when number of columns returned by Stored Procedure change. we often have to edit the SSIS Package to handle this situation.

We are going to use Script task so we don't have to worry if Stored Procedure definition change. As long as it will return us the data, we will dump into new Excel file on each execution.

here is my sample Stored Procedure. 

Create Procedure Dbo.usp_TotalSale
AS 
BEGIN 
Select * From [dbo].[TotalSale]
END


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
StoredProcedureName: Provide the Stored Procedure Name with Schema that you would like to execute and dump data to newly created Excel File.

Create Variables in SSIS Package to Create Excel File Dynamically from Stored Procedure in 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 execute Stored Procedure and Dump Data to Excel File on each execution


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.


Use variables in Script Task in SSIS Package to generate Excel dynamically from Stored Procedure



Step 4: Add Script to Script task Editor in SSIS Package To create Excel File for Stored Procedure Results
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 StoredProcedureName = Dts.Variables["User::StoredProcedureName"].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 =
                  "EXEC  " + StoredProcedureName;
                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+ ") 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.In script task, it is going to execute Stored Procedure and them dump the results returned by stored procedure to Excel file. 

I executed my SSIS Package couple of times and it generated excel files as shown below.

How to load Stored Procedure results to Excel File Dynamically in SSIS Package by using Script Task -C # Scripting Language




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

8 comments:

  1. great article, having trouble creating multiple worksheets with multiple stored procedures. What if I have another stored procedure and want to add the results to the same workbook but in a different sheet.

    ReplyDelete
  2. Great tutorial, but I am having timeout error in script task, running SP that runs for 6min, but it stops after 30s I tried adding this line to script, but it did not work.
    DataSet ds = new DataSet();
    adapter.Fill(ds);
    adapter.SelectCommand.CommandTimeout = 1800;
    Do you know how to upgrade this script to not have timeout error

    ReplyDelete
  3. Hi,
    How to run the Stored procedure with passing parameter

    ReplyDelete
  4. I am newer to this and tried to perform this and get the following error:
    System.InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized.
    at System.Data.Common.DbDataAdapter.GetConnection3(DbDataAdapter adapter, IDbCommand command, String method)
    at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
    at ST_b9b0ce053b4249359fdbd25b9a37dfbf.ScriptMain.Main()

    ReplyDelete
    Replies
    1. I am getting the same error. Where you able to fix it?

      Delete
    2. I thing when you create the 'FolderPath' variable , you should put a '\' in end of the value string.

      Delete
  5. I had trouble exporting the results to excel because of single quotes in my output so I modified the code to handle single quotes.

    int index = table.Rows.IndexOf(row);
    var columnValue = table.Rows[index].ItemArray[i].ToString();
    if (table.Rows[index].ItemArray[i].ToString().Contains("'"))
    {
    columnValue = columnValue.Replace("'", "''");
    }
    columnvalues += "'" + columnValue + "',";

    ReplyDelete
  6. I am getting an error with the following line. I stepped through the code and this is the line that threw the error.

    adapter.Fill(ds);


    Here is what I have in the error log.
    System.InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized.

    at System.Data.Common.DbDataAdapter.GetConnection3(DbDataAdapter adapter, IDbCommand command, String method)

    at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

    at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)

    at ST_9ea14ce8a4ac4b5d8e98b991a3e93588.ScriptMain.Main() in C:\Users\rgd\AppData\Local\Temp\Vsta\32133da67d6a4854aec67e3324183b76\ScriptMain.cs:line 126

    Any ideas what I did wrong?

    ReplyDelete