Execute DTS from VB

[dom24]

Hi,

I'm executing a DTS package that I created in SQL Server from MS Access using hte following code:

Private Sub Staff_Click()
Dim objPkg

Set objPkg = CreateObject("DTS.Package2")
objPkg.LoadFromSQLServer "NTSLDEVSQL", "trainingevaluationuser", "trainingevaluationuser", , , , , "trainingEvaluation_updatestaff"
objPkg.Execute

Set objPkg = Nothing

End Sub

This works fine, but I want it to tell the user that the package has been run. Can someone please tell me how I do this? Or give me an example?
Thanks.

 

[kmclane]

Try using the Messagebox() function:

Messagebox("The Process Completed Successfully", _ VBOKOnly)

Of course you'll need to check for the success or failure, but you can use an if and two different messages to let the user know the result.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[tb]

Just a few things that I use ...

'Set object to raise an error on failure and to do eventlogs
objPackage.FailOnError = True
objPackage.WriteCompletionStatusToNTEventLog = True

'Run DTS Package
objPackage.Execute

'This is advised before setting = Nothing
objPackage.UnInitialize

Set objPackage = Nothing

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[yksvaan]

If you have IIS SMTP running on the machine where you are executing that script, and your DNS can route the mail addresses you can send mail with VBScript. You can of course build subject and body strings so that they have for instance dtspackage's name and current date and so on. And you could put that script as the last task in the dts package itself.

Cheers

set oMail=CreateObject("CDONTS.NewMail")

oMail.from="adminorsomething@myfirm"
oMail.to="user@myfirm"
oMail.MailFormat=0
oMail.subject="DTS package has been run"
oMail.body="DTS package has been run rgds admin"

oMail.send

set oMail=Nothing

 

[dom24]

Thanks very much. :0)