Creating Worksheets from multiple stored procedures in SSIS script task

Southern_Gentleman
Southern_Gentleman used Ask the Experts™
on
I want to create a excel spreadsheet directly from a SSIS script task which is being pulled from a stored procedure. I'm able to create single sheet workbook with one stored procedure but I'd like to add another another sheet to the same workbook but from a different stored procedure.  I'm taking this from an example from techbrothersIT

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace ST_563e77770d454d11b11087ae4d41268c
{
    
	[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{
    
        public void Main()
        {
            string datetime = DateTime.Now.ToString("yyyy_MM_dd_HHmmss");
            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 StoredProcedureName2 = Dts.Variables["User::StoredProcedureName2"].Value.ToString();
                string SheetName = Dts.Variables["User::SheetName"].Value.ToString();
                string SheetName2 = Dts.Variables["User::SheetName2"].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["DB_ConnectionTo_Scale"].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;

                }
            }
        }

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>I want to create a excel spreadsheet directly from a SSIS script task which is being pulled from a stored procedure.
Should be able to do this within SSIS with multiple data flow tasks that use different Excel destination connections, making sure you have named ranges in your Excel doc and refer to them in these SSIS Excel connections.

If you're open to another approach, here's an article I wrote five years ago (so it's a bit outdated) on how you can call SQL Server Stored Procedures from within Excel docs. Microsoft Excel & SQL Server:  Self service BI to give users the data they want

( edited after original post )

Author

Commented:
sorry not what i was looking for. since i want to schedule and email the task at different times of the day.
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>I want to create a excel spreadsheet directly from a SSIS script task
I propose..
  • Manually create an Excel doc that is cosmetically PERFECT as far as what you are hoping the SSIS package will create.  I'll call this a 'template file'.  Create named ranges for the multiple locations you wish a SP to export rows, and remember those names.
  • Save the file anywhere in your network the SSIS package can access.
  • In your SSIS package in the beginning create a File System task to COPY that template file to your ultimate destination.
  • In your SSIS package create multiple Excel connections for your multiple destinations, making sure you get the named range right for each one.  Also set the Delay Validation (?) property to True so when the SSIS package opens it doens't immediately attempt to validate a file that is not there yet.
  • Create your multiple data flow tasks, each one with an Excel destination that maps to each Excel connection/different named range.
  • Then send the Excel doc in your email.

Bingo bango.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial