Running a DTS from Access VBA

[GJP55]

Not sure if this should be in the Access VBA forum or SQL Forum so I will try here first.

I am running a DTS from within an Access module and trapping errors to display to the user if the package has ran ok.

The code I have is as follows:

Function ExecPackage(sPackageName As String) As Boolean

Dim oPKG As DTS.Package, oStep As DTS.Step
Set oPKG = New DTS.Package

varReturn = SysCmd(acSysCmdSetStatus, "Running DTS Package...&quot

Dim sServer As String
Dim sMessage As String
Dim lErr As Long, sSource As String, sDesc As String

' Set Parameter Values
sServer = "Server"

' Load Package
oPKG.LoadFromSQLServer sServer, , , _
DTSSQLStgFlag_UseTrustedConnection, , , , sPackageName

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

oPKG.Execute

' Get Status and Error Message
For Each oStep In oPKG.Steps

If oStep.ExecutionResult = DTSStepExecResult_Success Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
Debug.Print sMessage & " " & oStep.Description & " Successfull"
End If

If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
Debug.Print sMessage & " " & oStep.Description
& " Failed"
End If
Next

oPKG.UnInitialize

Set oStep = Nothing
Set oPKG = Nothing

End Function

The DTS package name is passed to the function and for testing purposes, I am looking at the messages in the debug window. This code runs fine showing all steps in the DTS but it is not displayed in the running order of the DTS.

Is there any way I can change the code so that displays in the order that the DTS runs?. From that I could take the first failure to display to the User where the DTS has failed (ie.Importing of a file failed or writting file to history, etc.)

Thanks

 

[zooraw]

What references do you have to include in order to get this to work?

Thanks....

 

[GJP55]

Microsoft DTSPackage Object Library

 

[zooraw]

Don't know why I didn't see that until you said that. I think I included every other reference but that one....

Thanks!

 

[GJP55]

No problem, glad for the first time I can help somebody else !

 

[balistikb]

I am looking to do the same thing from access. I need to run a dts from access. It is a simple run, no logging or anything extra. I would just like the user to be able to run the DTS from access. Could someone please help?

 

[thermidor]

I'm not sure what exactly you're trying to accomplish, but this thread might be helpful

http://www.tek-tips.com/viewthread.cfm?SQID=878673&SPID=329&newpid=329&page=1

 

[balistikb]

Well what I am trying to do is have a simple way for a user to run a DTS from access 2000.

 

[thermidor]

Can't you create a VBS file like the on in the example and then use VBA in Access to run the VBS file?