Refresh Excel Spreadsheets in SSIS 2012 script task using visual basic

You may also check out: Save As a new Excel file in SSIS 2012
Save or Publish an Excel Spreadsheet to HTML in SSIS
Delete Excel Spreadsheets in SSIS 2012
Copy & Paste Special Excel Spreadsheet
Excel manipulation script SSIS package possible errors
Embedded excel report in the email using SSIS

Refresh Excel Spreadsheets with Sql Server Integration Services 2012 script task using visual basic language.

Environment:

Sql Server Integration Services 2012
Office Excel 2010
Windows 8


' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.

Imports System
Imports System.Data
Imports System.Math
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.SqlServer.Dts.Runtime

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    Public Sub Main()

        Dim Exl1 As New Excel.Application
        Exl1.Visible = False
        Dim IntPtr1 As IntPtr = Exl1.Hwnd

        Try

            Dim EW As Excel.Workbook

            EW = Exl1.Workbooks.Open("C:\demo\RefreshExcel.xlsx")
            EW.RefreshAll()
            EW.Save()
            EW.Close()
            Exl1.Quit()
            Exl1.Visible = True
            Runtime.InteropServices.Marshal.ReleaseComObject(Exl1)
            For Each proc As System.Diagnostics.Process In System.Diagnostics.Process.GetProcessesByName("Excel")
                If proc.MainWindowHandle = IntPtr1 Then
                    proc.Kill()
                End If
            Next
            Exl1 = Nothing
            Dts.TaskResult = ScriptResults.Success
        Catch ex As Exception
            Exl1.Quit()
            Exl1.Visible = True
            Runtime.InteropServices.Marshal.ReleaseComObject(Exl1)
            For Each proc As System.Diagnostics.Process In System.Diagnostics.Process.GetProcessesByName("Excel")
                If proc.MainWindowHandle = IntPtr1 Then
                    proc.Kill()
                End If
            Next
            Exl1 = Nothing
            Dts.TaskResult = ScriptResults.Failure
        End Try
    End Sub

End Class

Or click the following picture for a better display:

refresh_excel_ssis

You may also check out: Save As a new Excel file in SSIS 2012
Save or Publish an Excel Spreadsheet to HTML in SSIS
Delete Excel Spreadsheets in SSIS 2012
Copy & Paste Special Excel Spreadsheet
Excel manipulation script SSIS package possible errors
Embedded excel report in the email using SSIS

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>