Thursday, August 18, 2011

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
                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..

No comments:

Post a Comment