I have a DTS package that calls another DTS package. For the first time, I noticed behavior where the subordinate package fails, but the calling package did not error and stop processing (all using the 'continue on success' workflow properties).
unlike other packages that do this properly without issue, i went ahead checked the "fail package on first error" property under error handling on the subordinate package - then the calling package stopped on that step as desired.
my theory is that the type of error causing the subordinate package to stop was not reported as an error in the same manner to the calling package - that's a feature or bug i'm not sure ?!
here is some background on the "Fail package on first" property :
The Fail Package on first error option (FailOnError property, Package object) will force a package to terminate if any step raises an error. This option is set in the Error Handling section on the Logging tab of the Package Properties dialog. Whilst not strictly a workflow related property, it can have an effect on the behaviour of your workflow. When this option is on any On Completion or On Failure constraints are redundant as they will never be followed in the case of the related task failing.
Similar behaviour to Fail Package on first error can be set on an individual step basis using the Fail package on step failure option (FailPackageOnError property, Step2 object). This causes the same behaviour described above, but only where the task that fails is controlled by a step with this option set.
here's a completely different reason the package may return success even when a step fails.
BUG: DTS May Return a Success Even When it Fails to Transfer Data
http://support.microsoft.com/?kbid=301383