Avatar of Southern_Gentleman
Southern_GentlemanFlag 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
	public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        public void Main()
            string datetime = DateTime.Now.ToString("yyyy_MM_dd_HHmmss");
                //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();

                //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);

                //Use OLE DB Connection and Create Excel Sheet
                Excel_OLE_Con.ConnectionString = connstring;
                Excel_OLE_Cmd.Connection = Excel_OLE_Con;
                Excel_OLE_Cmd.CommandText = "Create table " + SheetName + " (" + TableColumns + ")";

                //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;


                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"))
                    Dts.TaskResult = (int)ScriptResults.Failure;


Open in new window

C#Microsoft SQL ServerSSISMicrosoft Excel

Avatar of undefined
Last Comment
Jim Horn
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

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


sorry not what i was looking for. since i want to schedule and email the task at different times of the day.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo