Listing 2: Custom Script That Deletes Older Files

'BEGIN COMMENT
'Add namespaces
'END COMMENT
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
'BEGIN CALLOUT A
Imports System.IO
'END CALLOUT A

'BEGIN COMMENT
'Define the ScriptMain class
'END COMMENT
Public Class ScriptMain

'BEGIN COMMENT
    'Define the Main subroutine
'END COMMENT
    Public Sub Main()

'BEGIN COMMENT
        'Create a Try/Catch block
'END COMMENT
        Try
'BEGIN CALLOUT B
'BEGIN COMMENT
            'Define variable to hold file name
'END COMMENT
            Dim RptFile As String = _
                DirectCast(Dts.Variables("RptFile").Value, String)
'BEGIN COMMENT
            'Define variable to hold file attributes
'END COMMENT
            Dim FileObj As New FileInfo(RptFile)
'BEGIN COMMENT
            'Define variable to hold file's last modified date
'END COMMENT
            Dim FileTime As Date = FileObj.LastWriteTime
'BEGIN COMMENT
            'Define variable to hold package execution start time
'END COMMENT
            Dim StartTime As Date = _
                DirectCast(Dts.Variables("StartTime").Value, Date)
'BEGIN COMMENT
            'Define variable to hold time interval
'END COMMENT
            Dim DateHours As Long = _
                DateDiff(DateInterval.Hour, FileTime, StartTime)
'END CALLOUT B

'BEGIN CALLOUT C
'BEGIN COMMENT
            'Delete file if time interval over 48 hours
'END COMMENT
            If DateHours > 48 Then
                File.Delete(RptFile)
            End If
'END CALLOUT C

        Catch e As Exception
'BEGIN CALLOUT D
'BEGIN COMMENT
            'Raise an error event if exception
'END COMMENT
            Dts.Events.FireError(-1, "Script task", _
                "Cannot delete file. " + e.Message, "", 0)
'END CALLOUT D
'BEGIN CALLOUT E
'BEGIN COMMENT
            'Inform runtime that task failed
'END COMMENT
            Dts.TaskResult = Dts.Results.Failure
'END CALLOUT E

        End Try

'BEGIN COMMENT
        'Inform runtime that task succeeded
'END COMMENT
        Dts.TaskResult = Dts.Results.Success

    End Sub

End Class





1