Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Hiding Excel Sheets from a Script Task - SQL Server Integration Services

RSS
Modified on Fri, May 01, 2009, 10:43 AM by Administrator Categorized as SSIS (SQL Server Integration Services)
In SQL Server Integration Services, the Script Task allows you to code in VB.NET. However, you're not allow to set references to COM objects, including the Excel object model. The following code provides a example of a work-around. The key is the CreateComObjectFromProgId function. Be sure to set Option Strict Off in the module where you use this code.

Public Sub Main()

    Dim app As Object

    Try

        app = CreateComObjectFromProgId("Excel.Application")

        If app IsNot Nothing Then

            Dim wbk As Object = app.Workbooks.Open("C:\Data\ExcelTest.xls")

            If wbk IsNot Nothing Then

                Dim sheet As Object = wbk.Sheets("Sheet3")

                If sheet IsNot Nothing Then

                    sheet.Visible = 0 '0 = hidden
                    wbk.Save()
                    app.Quit()

                End If

            End If

        End If

    Catch ex As Exception

        app.Quit()

    End Try

    Dts.TaskResult = Dts.Results.Success

End Sub

Private Function CreateComObjectFromProgId(ByVal progId As String) As Object

    Dim oType As Type = Type.GetTypeFromProgID(progId)

    If oType Is Nothing Then
        Return Nothing
    Else
        Return Activator.CreateInstance(oType)
    End If

End Function

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2017, Patrick Jasinski.