'This is a "sketch" of handling a DTS package programmatically. 'For more info' check out the following MSKB articles: ' '"Q221193 - INF: How To Install DTS Event Handlers In Visual Basic" ' '"Q240221 - INF: How To Handle Errors in DTS Package and Step Objects" ' '"Q252987 - INF: Execute a SQL Server DTS Package from Active Server Pages" ' 'Any calls to routines beginning with "bas" have nothing to do with DTS 'and should be disregarded in this context. ' 'DTS variables. Private WithEvents p_objDTSPack As DTS.Package Private p_objTaskSQL As DTS.ExecuteSQLTask Private p_objTaskPump As DTS.DataPumpTask Private p_blnCancel As Boolean

Private Sub p_objDTSPack_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, _ ByRef p_blnCancel As Boolean)

Call basAppInfoMsg(EventSource & ": " & Description, "DTS Error.")

End Sub

Private Sub p_objDTSPack_OnFinish(ByVal EventSource As String) Call basAppInfoMsg("DTS Package finished." _ & vbNewLine & "(" & EventSource & ")") End Sub

Private Sub p_objDTSPack_OnProgress(ByVal EventSource As String, _ ByVal ProgressDescription As String, _ ByVal PercentComplete As Long, _ ByVal ProgressCountLow As Long, _ ByVal ProgressCountHigh As Long) ' The DTS Package will trigger this event at certain intervals ' to report the progress of the package. This can be controlled ' by setting the DTS.Package.ProgressRowCount property.

Call basAppInfoMsg("DTS Package running…" _
    & vbNewLine & "(" & EventSource & ": " _
    & ProgressDescription & ")")

End Sub

Private Sub p_objDTSPack_OnQueryCancel(ByVal EventSource As String, _ ByRef p_blnCancel As Boolean) ' The DTS package will trigger this event at certain intervals to check ' whether the execution of the package should be terminated. Set ' p_blnCancel to true to cancel the execution of the package. If p_blnCancel Then _ Call basAppInfoMsg("DTS Package query cancelled." _ & vbNewLine & "(" & EventSource & ")") End Sub

Private Sub p_objDTSPack_OnStart(ByVal EventSource As String) Call basAppInfoMsg("DTS Package starting…" _ & vbNewLine & "(" & EventSource & ")") End Sub

Public Function basHits() As Boolean

'Import hits log file.

Call Me.basAppInfoMsg("Running…", "Bulk inserting hit log file...")

Set p_objDTSPack = VBA.CreateObject("DTS.Package")

With p_objDTSPack

    'The DTS package "ImportHitLog" was created in SQL server by hand.
    'Load package using NT security (dbDTSFlag_UseTrustedConnection = 256).
     Call .LoadFromSQLServer(ServerName:=dbSERVER, PackageName:="ImportHitLog", Flags:=dbDTSFlag_UseTrustedConnection)
    
    'Both of these Steps must run in the same thread for two reasons:
    'The Provider for the Bulk Insert Task does not support worker threads;
    'and the SQL Task must precede the Bulk Insert Task.
    .Steps("DTSStep_DTSExecuteSQLTask_1").ExecuteInMainThread = True
    .Steps("DTSStep_DTSBulkInsertTask_1").ExecuteInMainThread = True
    
    'Using the Execute method of the Package object
    'allows the Events (if any) to fire.
    .Execute

End With

End Function