none
Execute package task not inherit package execution result

    Dotaz

  • I have changed the maximum error count to 3, so that on the third error through execution report fail. On higher level I call this package through a control package using execute package task. 

    Now my package has 2 expected errors (I use a failure flow) and report success, then the execute package task report fails. Do I need to change something else too so that I will get success on execute package task?


    • Upravený AngelosPs pondělí 11. června 2018 12:42
    pondělí 11. června 2018 12:39

Odpovědi

  • Arthur If I set Propagate to False, then I can be informed for errors and my logic will work.

    But the next steps of the SQL job will run on other errors too  (like error downloading the file etc.), so after instead of debug the package I will have also to clean the DWH.


    Do not enable propagate on error for the tasks that you want to fail the package.

    Arthur

    MyBlog


    Twitter

    • Označen jako odpověď AngelosPs čtvrtek 14. června 2018 9:14
    středa 13. června 2018 13:45
    Moderátor

Všechny reakce

  • Hi AngelosPs,

    I do not know how you arrived to "2 expected errors" but I imagine your master / parent package errors out on the 1st failure of the child package (that runs the report).

    In this case set PropagateOnError to false

    Image result for propagation error ssis

    But honestly, counting number of errors is not serious, it is rarely needed in a report generation scenario. I would rethink your design.


    Arthur

    MyBlog


    Twitter

    pondělí 11. června 2018 13:23
    Moderátor
  • Hello Arthur, 

    thank you for your response. Maybe a printscreen is more explainable.

    • On the right part is the package where the MaximumErrorCount have been set to 3. So in that execution with 2 errors it executed with success.
    • On the left part EPT stands for execution package task, as a part of a control flow.

    So why the package is executed with success called from the execution package task, but the  execution package task reports failure??I guess should be a setting somewhere.

    úterý 12. června 2018 8:38
  • Hello Arthur, 

    thank you for your response. Maybe a printscreen is more explainable.

    • On the right part is the package where the MaximumErrorCount have been set to 3. So in that execution with 2 errors it executed with success.
    • On the left part EPT stands for execution package task, as a part of a control flow.

    So why the package is executed with success called from the execution package task, but the  execution package task reports failure??I guess should be a setting somewhere.

    I think it should be the Propagate variable

    The error will still get bubbled up the package level which causes EPT to report a failure

    Try setting Propagate variable to False by going to the On Error event handler for first package task where you've set MaxErrorCount to 3

    see

    http://datachix.com/2011/02/08/messing-with-errors-in-ssis-or-does-the-propagate-property-really-exist/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    úterý 12. června 2018 8:52
  • Hi Visakh16,

    thank you for you response. 

    If I get 3 errors for that package (the right one) and propagate variable is false, then I want to get a failure also on the control package (left one).Is that the expected behavior?

    úterý 12. června 2018 9:12
  • Hi Visakh16,

    thank you for you response. 

    If I get 3 errors for that package (the right one) and propagate variable is false, then I want to get a failure also on the control package (left one).Is that the expected behavior?

    Nope

    If you set propagate to false errors wont get bubbled up and package wont fail

    But as long as you fail the package, your EPT in the other package will not give success either

    So its a mutually exclusive situation

    If your main intention is to make sure EPT doesnt fail then you should set propagate to False and make left package not fail regardless of the number of errors

    You can always capture the errors happening inside the package to a table or a file inside On Error error handler where you've set Propagate to false. That would be the ideal solution if you dont want EPT to fail which will also cause your right package to fail under default settings


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    úterý 12. června 2018 9:34
  • If you check the package (right screen), if the package fails then there is an precedence constraint where when the first data flow task fails (and so the for loop container) then go to a second flow.

    Ideally I want EPT to fail when there are 3 errors, because that will show me that it's not expected. 

    The logic behind that package:

    Download several json files,  in DFT ,inside FOR_PROCESS (for loop container) there is a script transformation task which reads the json file. If that script tasks fails (means json format is wrong), then fail DFT and FOR_PROCESS. 

    Follow precedence constraint on failure, send mail that 2nd flow starts (red line on right part)and load yesterday file.

    If everything was success, report success. If you get 2 errors then report success. If errors>3, report failure on EPT.  Could you one more explain to me something? In the picture right part left corner the package report "Package execution completed with success", but on the left part (EPT shows error) left corner "package execution completed with error".Why? I used maximumerrorcount, so to report success if errors are 2.

    úterý 12. června 2018 9:57
  • Again I repeat

    Ideally I want EPT to fail when there are 3 errors, because that will show me that it's not expected

    For this you need to set Propagate to False (the default) in the other package which the EPT calls

    But when this happens automatically the package containing the EPT also fails (if you've not set Propagate variable to False for the EPT inside the left  package)

    If everything was success, report success. If you get 2 errors then report success. If errors>3, report failure on EPT

    This is where problem comes. Regardless of MaxErrorCount setting, failure of package is determined by the propagate variable value. So if you set is as True, regardless of MaxErrorCount, errors will get bubbled and faile EPT

    If you set this to False, EPT will never get failed

    I think what you need to do is to set Propagate to False

    Then have a logic inside OnError event handler to log the errors on to a table

    then as a last step in your right package do a count of errors raised within current execution and if it is beyond the limit (>=3 in your case), just set ForcedExecutionResult to Failure 
    This will force package to fail when errors >=3 and EPT will fail. All other cases EPT will be success which is what you wanted I guess


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    úterý 12. června 2018 10:43
  • Do you have any thought how I can count errors?Which variable I need to use?

    I just saw that through system variable ErrorCode, I get multiple 'errors' per error like Microsoft SQL Native Client, SSIS. Correct?

    • Upravený AngelosPs úterý 12. června 2018 12:36
    úterý 12. června 2018 12:07
  • The ErrorCode(s) may and typically are related to the same error.

    You can instead try to handle the errors gracefully on the right side irrespective to the count of errors because you want to continue the execution.

    So by just setting the OnError event handler to false you stop them from bubbling upstream.

    Graceful error handling usage pattern.


    Arthur

    MyBlog


    Twitter

    úterý 12. června 2018 13:11
    Moderátor
  • Arthur If I set Propagate to False, then I can be informed for errors and my logic will work.

    But the next steps of the SQL job will run on other errors too  (like error downloading the file etc.), so after instead of debug the package I will have also to clean the DWH.

    středa 13. června 2018 8:26
  • Visakh16 

    I tried to use on error event, instead of the constraint but the logic inside the event is executed twice. Can you think of any possible reason?

    středa 13. června 2018 13:24
  • Arthur If I set Propagate to False, then I can be informed for errors and my logic will work.

    But the next steps of the SQL job will run on other errors too  (like error downloading the file etc.), so after instead of debug the package I will have also to clean the DWH.


    Do not enable propagate on error for the tasks that you want to fail the package.

    Arthur

    MyBlog


    Twitter

    • Označen jako odpověď AngelosPs čtvrtek 14. června 2018 9:14
    středa 13. června 2018 13:45
    Moderátor
  • But based on the propagate function, I have to use the same on the EPT of that package in the control package. And also in the sequence that this package is executed.Correct?
    středa 13. června 2018 13:59
  • You can operate on a more granular level as the task itself.

    Arthur

    MyBlog


    Twitter

    středa 13. června 2018 14:00
    Moderátor
  • You can operate on a more granular level as the task itself.

    Arthur

    MyBlog


    Twitter

    I didn't understood, could you please re-phrase that sentence?
    středa 13. června 2018 14:08
  • You basically need something like

    Image result for ssis propagate on error for task

    Where you disable propagation of errors at a smaller as say container level, for example a sequence container as per above:


    Arthur

    MyBlog


    Twitter

    středa 13. června 2018 14:24
    Moderátor
  • I think I am one step closer. Really thank you guys for helping me out.I never used before these settings.

    Attached the design based on your input ArthurZ. Settings MaximumErrorCount=3 to pkg & Propagate=False to (1)FOR_PROCESS & (4) Sequence Container. I tried setting propage= false to 2 DFT_STG, but when it fails the (1)FOR_PROCESS is going to next file which is not the expected attitude.

    On left side (5), should be succeeded, probably that is happening due to (right side 2) failure.

    středa 13. června 2018 16:13
  • Just mark a post of ArthurZ as an answer, to show that is doable with some downsides (In order propagate to work, it's needed to use propagate also to the left part. Meaning that the control package will always executed with success).

    My solution was to add a variabla inside the 2 DFT_STG, there is a script component. On that one I use a write variable on the try...catch. And based on that, I will continue to the flow. Thank guys for your time and input. 

    čtvrtek 14. června 2018 9:20
  • Visakh16 

    I tried to use on error event, instead of the constraint but the logic inside the event is executed twice. Can you think of any possible reason?

    you add on error event for which task?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    čtvrtek 14. června 2018 9:40