DTS Package Events in Visual Basic

DTS Package Events in Visual Basic

To handle Data Transformation Services (DTS) package events in Microsoft® Visual Basic®, declare the Package object variable WithEvents. A Package2 object variable cannot be declared WithEvents. If you want to use the new features of the Package2 object and also handle package events, create a Package object and assign it to a Package2 object variable. You can use this object to access the package properties and methods. Also assign the Package2 object variable to a Package object variable that has been declared WithEvents.

After you have declared a Package object variable WithEvents, you must provide event handlers for all the package events. If you fail to do so, you will typically receive an access violation error at the time the unhandled event is raised.

Because Visual Basic objects do not support multiple threads, you also must set the ExecuteInMainThread property to TRUE for each Step object in the package.

Example

The following code example is a private class that handles package events after its PackageObj property has been set.

Note  The OnQueryCancel and OnStart event handlers consist of only a comment. This comment satisfies the requirement that a handler be supplied for these events and causes Visual Basic to compile the Subs. You also can use the single statement Exit Sub.

Option Explicit
Private WithEvents objPackage     As DTS.Package
. . .
Private Sub objPackage_OnError(ByVal EventSource As String, _
            ByVal ErrorCode As Long, ByVal Source As String, _
            ByVal Description As String, ByVal HelpFile As String, _
            ByVal HelpContext As Long, _
            ByVal IDofInterfaceWithError As String, pbCancel As Boolean)
    Dim sMsg        As String
    
    sMsg = "EventSource:   " & EventSource & vbCrLf & _
            "ErrorCode:       " & (ErrorCode) & vbCrLf & _
            "Source:             " & Source & vbCrLf & _
            "Description:   " & Description & vbCrLf & _
            "HelpFile:         " & HelpFile & vbCrLf & _
            "IDofIFWErr:     " & IDofInterfaceWithError
    MsgBox sMsg, vbExclamation, "OnError"

End Sub

Private Sub objPackage_OnFinish(ByVal EventSource As String)
    MsgBox EventSource, vbInformation, "OnFinish"
End Sub

Private Sub objPackage_OnProgress(ByVal EventSource As String, _
            ByVal ProgressDescription As String, _
            ByVal PercentComplete As Long, _
            ByVal ProgressCountLow As Long, _
            ByVal ProgressCountHigh As Long)
    Dim sMsg        As String
    
    sMsg = "EventSource:           " & EventSource & vbCrLf & _
            "ProgressDescr:         " & ProgressDescription & vbCrLf & _
            "PercentComplete:       " & (PercentComplete) & vbCrLf & _
            "ProgressCountLow:     " & (ProgressCountLow) & vbCrLf & _
            "ProgressCountHigh:   " & (ProgressCountHigh)
    MsgBox sMsg, vbExclamation, "OnProgress"

End Sub

Private Sub objPackage_OnQueryCancel(ByVal EventSource As String, _
            pbCancel As Boolean)
    'MsgBox EventSource, vbInformation, "OnQueryCancel"
End Sub

Private Sub objPackage_OnStart(ByVal EventSource As String)
    'MsgBox EventSource, vbInformation, "OnStart"
End Sub

Public Property Get PackageObj() As DTS.Package2
    Set PackageObj = objPackage
End Property

Public Property Set PackageObj(ByVal oNewPack As DTS.Package2)
    Set objPackage = oNewPack
End Property