' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel

Public Class ScriptMain

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.
    Private _app As Microsoft.Office.Interop.Excel.Application
    Private _books As Microsoft.Office.Interop.Excel.Workbooks
    Private _book As Microsoft.Office.Interop.Excel.Workbook
    Protected _sheets As Microsoft.Office.Interop.Excel.Sheets
    Protected _sheet As Microsoft.Office.Interop.Excel.Worksheet

    Public Sub Main()
        ' Add your code here
        Try
            Dim FileName As String
            Dim Tab As String
            FileName = CType(Dts.Variables("DestinationFile").Value, String)
            Tab = CType(Dts.Variables("AccessTB").Value, String)
            OpenExcelWorkbook(FileName)
            _sheet = CType(_sheets(Tab), Microsoft.Office.Interop.Excel.Worksheet)
            _sheet.Select(Type.Missing)
            Dim range As Microsoft.Office.Interop.Excel.Range = _sheet.Rows("2:65536")
            range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp)
            NAR(range)
            NAR(_sheet)
            CloseExcelWorkbook()
            NAR(_book)
            _app.Quit()
            NAR(_app)
            Dts.TaskResult = Dts.Results.Success
        Catch ex As Exception
            MsgBox(ex.ToString())
        End Try
    End Sub

    Protected Sub OpenExcelWorkbook(ByVal fileName As String)
        'try 
        '{ 
        _app = New Microsoft.Office.Interop.Excel.Application()
        If _book Is Nothing Then
            _books = _app.Workbooks
            _book = _books.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
            _sheets = _book.Worksheets
        End If
        '} 
        'catch(Exception ex) 
        '{ 
        ' Console.WriteLine(ex.ToString()); 
        '} 

    End Sub
    Protected Sub CloseExcelWorkbook()
        _book.Save()
        _book.Close(False, Type.Missing, Type.Missing)
    End Sub
    Protected Sub NAR(ByVal o As Object)
        Try
            If Not o Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
            End If
        Finally
            o = Nothing
        End Try
    End Sub
    ' To avoid the variable locking problem use this method to read package variables.
    ' No read variables need to be declared in the "ReadOnlyVariables" list of the script 
    ' component if this method is used inside the code.
    Private Function ReadVariable(ByVal varName As String) As Object

        Dim result As Object
        Try
            Dim vars As Variables
            Dts.VariableDispenser.LockForRead(varName)
            Dts.VariableDispenser.GetVariables(vars)
            Try
                result = vars(varName).Value
            Catch ex As Exception
                Throw ex
            Finally
                vars.Unlock()
            End Try
        Catch ex As Exception
            Throw ex
        End Try

        Return result

    End Function

    ' To avoid the variable locking problem use this method to set package variables.
    ' No write variables need to be declared in the "ReadWriteVariables" list of the script 
    ' component if this method is used inside the code.
    Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
        Try
            Dim vars As Variables
            Dts.VariableDispenser.LockForWrite(varName)
            Dts.VariableDispenser.GetVariables(vars)
            Try
                vars(varName).Value = varValue
            Catch ex As Exception
                Throw ex
            Finally
                vars.Unlock()
            End Try
        Catch ex As Exception
            Throw ex
        End Try

    End Sub

End Class