Delete a single sheet in Excel using SSIS

Question

Due to some concurrency issues, I have been forced to create a workbook with multiple sheets. At the end of the execution, some sheets will have data and some wont. I create the sheets using an Execute SQL Task.

I am trying to loop through the workbook and delete the sheets which dont have more than a single row. In other words, delete the sheet if row count is not greater than 1. Any pointers on this question will be appreciated. Please let me know if you need more details on my question. Thank you in advance.

EDIT

Following is the script task that I got from MSDN. I modified it to a point where it can get to the excel sheet and make a count of the rows, now all I want to do is when the count = 1 then delete the sheet. Can someone help me here?

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

namespace ST_c346c80b4e6747688383c47a9f3e6f78.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion



            public void Main()
        {
            string count = "";
            string fileToTest;
            string tableToTest;
            string connectionString;

            fileToTest = Dts.Variables["ExcelFile"].Value.ToString();
            tableToTest = Dts.Variables["ExcelTable"].Value.ToString();

            Dts.Variables["ExcelTableExists"].Value = false;

                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                "Data Source=" + fileToTest + ";Extended Properties=Excel 8.0";
                string SQL = "SELECT COUNT (*) FROM [" + tableToTest + "$]";
                using (OleDbConnection conn = new OleDbConnection(connectionString))
                {
                    conn.Open();
                    using (OleDbCommand cmd = new OleDbCommand(SQL, conn))
                    {
                        using (OleDbDataReader reader = cmd.ExecuteReader())
                        {
                            reader.Read();
                            count = reader[0].ToString();
                            //if (count = 1)

                        }
                    }
                    conn.Close();
                }
                //return count;
            }

       }
}

EDIT

On further investigation, I found that I would need to add the excel interop assembly for this to work. I dont have that option because this solution will be ported to 140 different machines.

Answer 1

Sadly you cannot delete a sheet in an Excel using OLEDB, the best you can do is clear it of data using the DROP Table command

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                     "Data Source=" + fileToTest + 
                     ";Mode=ReadWrite;Extended Properties=Excel 8.0";

string SQL = "SELECT COUNT(*) FROM [" + tableToTest + "$]";

using (OleDbConnection conn = new OleDbConnection(connectionString))
{
    conn.Open();
    using (OleDbCommand CountCmd = new OleDbCommand(SQL, conn))
    {
        int RecordCount = (int)CountCmd.ExecuteScalar();

        if (RecordCount == 1)
        {
            SQL = "DROP TABLE [" + tableToTest + "$]";
            using (OleDbCommand DropCmd = new OleDbCommand(SQL, conn))
            {
                DropCmd.ExecuteNonQuery();
            }
        }
    }
    conn.Close();
}

NOTE: The use of Mode=ReadWrite. You may include/exclude the HDR=Yes/No but you must not include IMEX=1 if you want read/write access to the Workbook

BTW: There's no need to use a OleDbDataReader to read a single scalar result, use ExecuteScalar() instead.

The only way around this using OleDB is to copy the data you want to retain into a new excel file and replace the original one. If you do this however you will lose any formulas or formatting.