Thursday, March 29, 2012

Dts From Vb

Hi all,
I tryed posting this in VB section but no response, so I'm trying here.
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 SubUse a timer to start another thread to run the package then your thread can carry on executing.|||Is there a way to do it without another thread? Like an async call with ADO?

No comments:

Post a Comment