Can you imagine??

I wrote a topic before about it, but my view on it has changed a little.

Imagine there's a DTS 2000 package (Package A) which executes another
package (Package B).
Inside Package B, create simply an ActiveX task which causes the package to
fail. (Main = DTSTaskExecResult_Failure)
Also go to the Package Properties and in 'logging' select 'Log package
execution to SQL Server’ (fill in what's needed).
Do not select 'fail package on log error'.
Save Package B.
Now in Package A, create the task to execute Package B.
Then, create two ActiveX tasks: in the first one write 'call
msgbox("execution failed")'
and in the other write 'call msgbox("execution successful")'
Create an on-success workflow and an on-error workflow to the ActiveX tasks.
Save and execute Package A.
You should get the right result: you see a messagebox: 'execution failed'.

Ok no problem so far, here comes the big trick.
Create an on-insert trigger in [msdb].[dbo].[sysdtspackagelog].
Write something invalid that would cause an error on exection, e.g.: select
0/0 from sysdepends.
Save it and execute Package A again.

What happens? Exactly, you see a new messagebox: execution successful.
But that's not right!? Package B failed, but still seems to be succesful??
How come?

Ok, in reality you don't make such messy trigger, but what when you have one
which calls a Stored Procedure which accidentally ends up in an error?
This is the case with me now. It took a long time to discover this. It
happens only in sub packages. Is this a bug, or what to think about this?

Any ideas?

For errors in the child package to propogate up to the parent through the
Execute Package Task, you must set Fail on first error On within the child
package. It does mean you cannot use any clean-up tasks or similar which use
On Error workflow in the child.

Does this help?

--
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com


"R. Deen" wrote:

This doesn't matter in this case. I've tried this already, as well as other
error handling options in both parent and child packages. It seems to me that
the problem is that whether there was a transformation error or not, if
something goes wrong in the child package logging(for example, because of a
wrong result in a trigger on the log table), the entiry child package failed
or succeeded, depending on the 'fail package on log error' option.
If you have the opportunity for it, take the challenge to try this, and let
me know what you see. I'm curious for the result and even more curious for
the solution!



"Darren Green" schreef: