Delete Empty Excel work sheets dynamically through SSIS

Yesterday I visited the SSIS forum and come across this requirement (delete empty excel sheets dynamically from a excel workbook). In SSIS we have to write the Script in Script Task. We've to use COM API to do this.


Follow below steps to solve this problem.

Step1: Open your BIDS and Use a Script Task  under the control flow.

Step 2: Select the Script language in which you are comfortable. Here I'm using the VB Script and than click on the Edit Script button.

Step3: In the Script Editor you need to add the "Microsoft.Office.Interop.Excel.dll" in your reference.

if you want to use this DLL, following these steps:

• On the Project menu, click Add Reference.
• On the COM tab, locate Microsoft Excel Object Library, and then click Select. In Visual Studio 2008, locate Microsoft Excel 12.0 Object Library on the COM tab.
• Click OK in the Add References dialog box to accept your selections. If you are prompted to generate wrappers for the libraries that you selected, click “Yes”.

Step 4: you need to import the following reference in your code.

Imports Microsoft.Office.Interop.Excel

Step 5: Write the following code and change the appropriate path for your excel file ( you can use the SSIS variable for passing the file name)

Public Sub Main()
        Dim Wexcel As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
        Dim Wb As Workbook = Wexcel.Workbooks.Open("C:\emptyExcelSheet.xls")
        Dim wf As WorksheetFunction = Wexcel.WorksheetFunction


        For Each sheet In Wexcel.Worksheets 
            If wf.CountA(sheet.Cells) = 0 Then
                Wexcel.DisplayAlerts = False
                sheet.Delete()
                Wexcel.DisplayAlerts = True


            End If
        Next sheet
        Dts.TaskResult = ScriptResults.Success
    End Sub

Step 6: Build the code first and then close the script editor. Now you can execute your package and test the results.

Hope this helps you..