none
SSIS and SQL Agent - SQL Agent job failing RRS feed

  • Question

  • Fellow SSISers

    This is an old package - SSIS 2008. Yes, I know, I will migrate soon but I have issue now in this setup.

    I have an ssis package, with a forloop and a data flow inside it. Data flow is using a connection object .Net Providers/Oracle DB Provider for . Net.  My Data Flow and my forLoop have an ON ERROR event handler and each has Propogate FALSE. And, just for testing, I have MaximumErrorCount is false for each as well.

    These are oracle conntions of course. I have a couple connections that WILL fail. When I run this in SSIS, these couple that are supposed to fail, fail; the package keeps on going like it should.

    When I run this in SQL Agent, it failes because it is picking up the error from the two failures. I have never seen this before. I was thinking that the ON ERROR event handler with Propogate false would prevent this.

    Any ideas how to prevent SQL agent from detecting these errors when the package actually is fine manually.

    Thanks,

    MG

    Thursday, March 7, 2019 4:48 PM

Answers

  • The "propagate" events turn off the package failure, but not the job as a whole.

    Arthur

    MyBlog


    Twitter

    • Proposed as answer by Yang.Z Friday, March 8, 2019 5:18 AM
    • Marked as answer by mg101 Friday, March 8, 2019 11:38 AM
    Thursday, March 7, 2019 8:39 PM
    Moderator

All replies

  • if you run manually,it is success ?

    Try yo use proxy account for security and permission issue.


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Thursday, March 7, 2019 5:08 PM
  • Yes, the dataflow task fails on two of the loops but it still finishes. Just like I designed it. It is not a security issue, it is process flow. And the SQL Agent job runs under admin authority.

    And FYI, I did try a proxy and no change.

    MG


    • Edited by mg101 Thursday, March 7, 2019 6:12 PM clarifiaciotn
    Thursday, March 7, 2019 6:10 PM
  • Hi mg101,

    You would need to set the Agent to ignore the error then.


    Arthur

    MyBlog


    Twitter

    Thursday, March 7, 2019 6:13 PM
    Moderator
  • HI Art,

    Thanks, Yes, I could and that and it would fix it. Or I could alter the SSIS package to say MaximumErrors to 0 - which is what I ended up doing.

    But why should I do either of those. I was thinking the Propogate to FALSE in the On Error Event Handlers would handle all of that. Nothing should be bubbling up to SQL Agent.

    MG

    Thursday, March 7, 2019 6:41 PM
  • The "propagate" events turn off the package failure, but not the job as a whole.

    Arthur

    MyBlog


    Twitter

    • Proposed as answer by Yang.Z Friday, March 8, 2019 5:18 AM
    • Marked as answer by mg101 Friday, March 8, 2019 11:38 AM
    Thursday, March 7, 2019 8:39 PM
    Moderator
  • Thanks Arthuz

    MG

    Friday, March 8, 2019 11:38 AM