• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 729
  • Last Modified:

DTS workflow problem: On_Failure task not executed, package fails

Hi,

I have the exact same error that was discussed at http://www.tek-tips.com/viewthread.cfm?qid=741644 with no solution.
In short, I return  DTSStepExecResult_Failure (or  DTSTaskExecResult_Failure, doesn't make a difference) from an ActiveX task and the task I defined to be executed on failure doesn't get executed but therefore the whole package fails.

Does anyone have an idea?

Thanks in advance,
AIndy
0
AIndy
Asked:
AIndy
  • 6
  • 6
1 Solution
 
Brendt HessSenior DBACommented:
Sometimes, it depends on what the failure that you are trapping is.  Certain errors encountered by an ActiveX task are not handled by the standard error handler.  Instead, an unhandled (by anything) error is generated.

You may want to include a test in the On_success branch for the DTCStepExecStatus, and see if it is <> 4 (Completed).  If so, you should throw an error.

Some discussions also talk about this issue (as you have seen at tek-tips) without coming to a conclusion.  The above are my opinions.

One final place to look for assistance is this message series on Google:

http://groups.google.com/group/microsoft.public.sqlserver.dts/browse_thread/thread/b59c6da0cb3b752b/152d37c1ec0f45b4?lnk=st&q=DTSStepExecResult_Failure&rnum=1&hl=en#152d37c1ec0f45b4

It discusses a DTS helper COM object to probe error values and others inside an ActiveX script.  Looks like it would be very helpful to you.
0
 
Anthony PerkinsCommented:
Post your code from your ActiveX script.
0
 
AIndyAuthor Commented:
Thanks bhess, acperkins.
Bhess, what do you mean by the test in the On_success branch? The on_success branch works fine, the other one doesn't..

The relevant code from my Active X script is

        If objFSO.FileExists(workPath & focusFile) then
        begin
                 objFSO.CopyFile workPath & focusFile, workPath & "input.xls", true
                 Main = DTSTaskExecResult_Success
        else
                 Main = DTSStepExecResult_Failure
                   'Main = DTSTaskExecResult_Failure
         end if

I assured that the else-branched is reached in case the file doesn't exist.

AIndy
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Anthony PerkinsCommented:
Here is the correct syntax:

       If objFSO.FileExists(workPath & focusFile) then
                objFSO.CopyFile workPath & focusFile, workPath & "input.xls", true
                Main = DTSTaskExecResult_Success
       else
                Main = DTSTaskExecResult_Failure
       end if
0
 
AIndyAuthor Commented:
acperkins, in how far is that supposed to make any difference?
0
 
Anthony PerkinsCommented:
>>in how far is that supposed to make any difference?<<
Well, for one it will run.  Your syntax won't even run.  There is no Begin in a VBScript.
0
 
AIndyAuthor Commented:
Well, it did run before. However, do you have any idea concerning the real problem?
0
 
Anthony PerkinsCommented:
>>Well, it did run before.<<
Fair enough.  Good luck.
0
 
AIndyAuthor Commented:
I guess that's a "no"?
Anyway, thanks for the syntax-hint...

Anyone else have an idea?
0
 
Anthony PerkinsCommented:
Not exactly, it just means I no longer wish to contribute to this thread.
0
 
AIndyAuthor Commented:
Don't get offended so quickly. I am sorry that I didn't treat your syntax hint with more respect, but believe me somehow this was ignored in my Script and it did run...
I hope I didn't hurt your feelings...
0
 
Anthony PerkinsCommented:
>> but believe me somehow this was ignored in my Script and it did run...<<
I believe you all right, I tested it :)  So I stand corrected.  It is just that I have no idea why it is allowed as it is not a VBScript keyword.

As to your original question.  Try the following exercise:
1. Create a new DTS Package
2. Add a Boolean Global Variable called "Success" and give it a value of -1
3. Add an ActiveX Script Task with the following code:
      Function Main()

      If  DTSGlobalVariables("Success").Value Then
            Main = DTSTaskExecResult_Success
      Else
            Main = DTSTaskExecResult_Failure
      End If

      End Function
4. Add an ActiveX Script Task with the following code:
      Function Main()

      MsgBox "Success"
      Main = DTSTaskExecResult_Success

      End Function
5. Add an ActiveX Script Task with the following code:
      Function Main()

      MsgBox "Falure"
      Main = DTSTaskExecResult_Success

      End Function
      
6. Add a "Success" Workflow between the first and second ActiveX Script Task.
7. Add a "Failure" Workflow between the first and third ActiveX Script Task.
8. Execute the DTS package.  You should see a Message Box with "Success"
9. Change the Success Global Variable to 0
10. Execute the DTS package.  You should see a Message Box with "Failure"

If that is the case, than compare it to your code in the package that is not producing the right results.
0
 
AIndyAuthor Commented:
Hi Acperkins,

thanks for your help. I actually found the mistake in my workflows. The problem wasn't the ActiveX-Script or its return-code, it was actually that the script on the error-branch was also dependent on the success of another task which itself was dependent on the success of the original ActiveX script. That's why the task on the error branch was never executed. Even though this sounds like a stupid mistake, it actually made sense at the time I designed it because I wanted the same script (which handled a loop) to be run at the end of two different branches.

Anyway, I found this out after reducing the complexity of the workflow according to the test you suggested. Therefore you deserve many thanks and the points :-)

AIndy
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now