Hi all,
I am currently facing a problem structured like this:

Our Server can not send mails due to some configurations applied by our
firewall.
To work around that I created a StoredProcedure which is using CDO to
archive the same goal.
....so far, the SP works as expected and sends mails.

Now the tricky stuff. I want to call the procedure whenever a DTS fails.
Currently I implemented the SQL Task that calls the SP manually (on failure)
in the DTS.
As you all can imagine, a lot of red lines appeared in my DTS designer

Anyway... I now try to get a hand on the error that occures and pass it to
the SP as a parameter.
Currently I just created a Global Variable holding the DTS Package Name (I
type it in manually).

Is there a way to get the error (incl. descriptions) that makes the DTS to
fail and can
I read the name of the package itself without manually assigning it to a GV
passing all as parameter to the SP?


Any ideas are kindly appreciated,
thanks in advance

Andreas Bretl
Software Engineer
bretl (AT) brainlab (DOT) com

See this code

Private Sub RunDTSPackage( )
Dim objPackage As New DTS.Package
. . .
On Error GoTo PackageError
. . .
objPackage.FailOnError = True
objPackage.Execute
Exit Sub

PackageError:
Dim sMsg As String
sMsg = "Package failed, error: " & sErrorNumConv(Err.Number) & _
vbCrLf & Err.Description & vbCrLf & sAccumStepErrors(objPackage)
MsgBox sMsg, vbExclamation, objPackage.Name
Exit Function
End Sub

Private Function sAccumStepErrors( _
ByVal objPackage As DTS.Package) As String
'Accumulate the step error info into the error message.
Dim oStep As DTS.Step
Dim sMessage As String
Dim lErrNum As Long
Dim sDescr As String
Dim sSource As String

'Look for steps that completed and failed.
For Each oStep In objPackage.Steps
If oStep.ExecutionStatus = DTSStepExecStat_Completed Then
If oStep.ExecutionResult = DTSStepExecResult_Failure Then

'Get the step error information and append it to the
message.
oStep.GetExecutionErrorInfo lErrNum, sSource, sDescr
sMessage = sMessage & vbCrLf & _
"Step " & oStep.Name & " failed, error: " & _
sErrorNumConv(lErrNum) & vbCrLf & sDescr &
vbCrLf
End If
End If
Next
sAccumStepErrors = sMessage
End Function

Private Function sErrorNumConv(ByVal lErrNum As Long) As String
'Convert the error number into readable forms, both hexadecimal and
decimal for the low-order word.

If lErrNum < 65536 And lErrNum > -65536 Then
sErrorNumConv = "x" & Hex(lErrNum) & ", " & CStr(lErrNum)
Else
sErrorNumConv = "x" & Hex(lErrNum) & ", x" & _
Hex(lErrNum And -65536) & " + " & CStr(lErrNum And
65535)
End If
End Function



Regards

Partha Mandayam
Software Consultant
Home page: http://partha.tripod.com


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks a lot Partha for the effords you had writing down all this,
that at least brought some motion into my brain and helped me understanding
it more precisely.
But it looks that , in order to archive my goal, I have to create somewhat
like a package executor to trap the errors in the way you described it. It
seems a great approach and I am thinking about changing my plans towards
something more close like yours.

But still I do not know if there is a way to get errornumbers or
packagename.
I tried to use something like this in my SQL-Task


INSERT INTO SomeTable
(ID, name, GlobalVar1)
VALUES
(1,'TestName', DTSGlobalVariables("MyVar").Value)

That did not work, but assigning it using a ? helped me out here:
INSERT INTO SomeTable
(ID, name, GlobalVar1)
VALUES
(1,'TestName',?)

? is referencing the Global Variable "MyVar"

But now what I am planning to do (until now I was able to deal with it).
How about the other way round? Are there standard variables that I could
query? Maybe like DTSGlobalVariables("DTSName").Value or
DTSGlobalVariables("LastError")Value. No need for a global variable I just
used it here to ilustrate the problem.

Any hints are still kindly appreciated,

Seize the Day

Andreas Bretl
Software Engineer
bretl (AT) brainlab (DOT) com