January 27, 2009 at 10:49 am
In the package properties, under Error Handling, there's the "Fail package on first error" option. This does exactly what I want it to do, but on the wrong moment; When an error occurs on a Task, I want to insert a line an audit table, and then give that "Fail Package" command. How can I do that?
(When I use the "Fail package on first error" option, the package immediately stops, before I can write a log-line.)
January 28, 2009 at 2:59 pm
What you need to do is add an "on failure" workflow to each task and have it point to a task that does the audit table insert. Then when a task fails, the audit task will execute before the package stops.
Greg
January 29, 2009 at 12:54 am
Thanks for your help Greg but this exactly how I have it right now but the package stops before the on failure step is executed for the audit table.
I'm using one main package to execute several other sub-packages. The audit problem is in a sub-package. When a step fails it doesn't execute the on failure step in that sub package, but it executes the on failure step in the main package. This actually is what I want but I also need to the audit step in the sub-package. So if there's a way to send the fail package command manually, that would do the trick for me.
January 29, 2009 at 1:15 am
I found something on MS TechNet, in VB, but I'n not sure on how to use it in DTS;
Option Explicit
Private WithEvents objPackage As DTS.Package
. . .
Private Sub objPackage_OnError(ByVal EventSource As String, _
ByVal ErrorCode As Long, ByVal Source As String, _
ByVal Description As String, ByVal HelpFile As String, _
ByVal HelpContext As Long, _
ByVal IDofInterfaceWithError As String, pbCancel As Boolean)
Dim sMsg As String
sMsg = "EventSource: " & EventSource & vbCrLf & _
"ErrorCode: " & (ErrorCode) & vbCrLf & _
"Source: " & Source & vbCrLf & _
"Description: " & Description & vbCrLf & _
"HelpFile: " & HelpFile & vbCrLf & _
"IDofIFWErr: " & IDofInterfaceWithError
MsgBox sMsg, vbExclamation, "OnError"
End Sub
Public Property Get PackageObj() As DTS.Package2
Set PackageObj = objPackage
End Property
Public Property Set PackageObj(ByVal oNewPack As DTS.Package2)
Set objPackage = oNewPack
End Property
http://technet.microsoft.com/en-us/library/aa176233(SQL.80).aspx#
January 29, 2009 at 10:25 am
henkvisser (1/29/2009)
I'm using one main package to execute several other sub-packages. The audit problem is in a sub-package. When a step fails it doesn't execute the on failure step in that sub package, but it executes the on failure step in the main package. This actually is what I want but I also need to the audit step in the sub-package. So if there's a way to send the fail package command manually, that would do the trick for me.
I haven't seen that behavior. What happens if you uncheck "Fail package on first error" in the sub-package?
Greg
January 30, 2009 at 12:31 am
Then the sub-package itself stops, but the main package continues since it doesn't get a failure back from the subpackage.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply