How to collect errors from DTS in application

Question

How to collect errors from DTS in application

Korolov

Grasshopper

Points: 12
More actions
April 13, 2007 at 1:01 pm

#68608

I have DTS created and executed from VB with multiple steps and tasks.
Everything ok if no errors happened. Problem started when error happened in any place within DTS, because we have to send an email to user with list of errors that happened during an execution. I found how to get an error back to VB.
To wrap it up I have an idea to create a job that will execute DTS get all errors and then send an e-mail.
Problem that I don’t know how to dynamically (from VB) create job.
Can anybody help me?
Or if you have a better idea how to collect errors from DTS in application, share please.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Matt Simmons SSCrazy Points: 2846 More actions · Answer 1

The easiest way is to create a proc (we called ours usp_execute_dts) that calls the DTS. You call the proc from your VB app.

The contents of the proc are:

go

CREATE PROCEDURE usp_execute_dts

AS

DECLARE @myline AS VARCHAR(300)

SET @myline = 'dtsrun /Sservername /Uuser /Ppassword /N"dts package name" +''

EXEC master..xp_cmdshell @myline

go

if you need to pass parameters to your DTS use the "/A" (ie. /Agvuser:8=' + @user).

Capture the errors in your DTS package and send them back via email as your indicated above.

Regards,
Matt

Korolov Grasshopper Points: 12 More actions · Answer 2

How can I capture the errors in my DTS package if I am already in VB and dtsrun doesn't return errors back to VB?

Matt Simmons SSCrazy Points: 2846 More actions · Answer 3

If your DTS is a series of steps all setup with on success and a final step of sending the "Success" email you can add a failure path to each step that has an Active X task that modifies the email variables and then sends the email.

Below is my Active-X code.

'**********************************************************************

' Visual Basic ActiveX Script

'************************************************************************

Function Main()

Dim Msg

FN = DTSGlobalVariables("glv_Import_File").value

Msg = "The File " & FN & " Was Successfully Imported."

DTSGlobalVariables("glv_Message").value = Msg

Main = DTSTaskExecResult_Success

End Function

By modifying the above you should be able to meet your needs.

Regards,
Matt