Avatar of Southern_Gentleman
Southern_Gentleman
Flag for United States of America asked on

Creating Worksheets from multiple stored procedures in SSIS script task

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

C#Microsoft SQL ServerSSISMicrosoft Excel

Avatar of undefined
Last Comment
Jim Horn

8/22/2022 - Mon
Jim Horn

>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 )
Southern_Gentleman

ASKER
sorry not what i was looking for. since i want to schedule and email the task at different times of the day.
ASKER CERTIFIED SOLUTION
Jim Horn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck