Hi all,
How would I make my DTS connection async. When I exeute SQL DTS package from VB, the program locks up until the DTS is done, so how would I make it async for it to not lock up?
Here is my Sub:
Public Sub ExecuteEDIPackage(FileName As Variant)

Dim sServer As String
Dim sUsername As String
Dim sPassword As String
Dim sPackageName As String
Dim lErr As Long
Dim sSource As String
Dim sDesc As String


sMessage = sMessage & "<<<Executing import on " & FileName & ">>>" & vbCrLf & vbCrLf

Set oPKG = New DTS.Package


' Set Parameter Values
sPackageName = "EDIPackage"

' Load Package
oPKG.LoadFromSQLServer DataSource, UserName, Password, _
DTSSQLStgFlag_Default, , , , sPackageName

' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next

' Execute
oPKG.Execute

' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
Else
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next

oPKG.UnInitialize

Set oStep = Nothing
Set oPKG = Nothing

sMessage = sMessage & "<<<Import on " & FileName & " is done.>>>" & vbCrLf & vbCrLf

End Sub