One SP is not running in SSIS and I don't know why

  • Hi everyone

    I have an SSIS package that has close to 3o SP that are run.  One container has 22 SP.  Of these 22, 1 is giving me problems.  Here are the tests I have run so far:

    Test 1 - Run all 22 SP -> the problem SP never finishes hence SSIS package never finishes

    Test 2 - Run 21 SP (ie all 22 excluding the problem SP) -> SSIS package finishes

    Test 3 - Run 1 problem SP (ie exclude all 22 except the problem SP) -> SSIS package finishes

    This means that something weird happens when the problem SP is included with all of the remaining 21 SP and they are all run together as one.  I just don't know what is going on here.  The problem SP is a super fast one that runs in under 1 minute on its own.

    I know without seeing the code it is hard to give specific guidance.  Based on your general experience, what could be the issue?  What should I look at or try to help troubleshoot this issue?  Is there a workaround you can suggest?

    The obvious solution is to exclude the problem SP from the SSIS package but that defeats the whole point of having SSIS in the first place (ie it saves me time by doing the ETL for me).

    Thank you

  • what happens if you move that SP to another container, being executed after the other ones?

    and most times its some settings on your sp's or locking between SP's - and bad error handling. or even a endless loop that only happens based on sequence of events, which would be related to the other SP's

  • frederico_fonseca wrote:

    what happens if you move that SP to another container, being executed after the other ones?

    and most times its some settings on your sp's or locking between SP's - and bad error handling. or even a endless loop that only happens based on sequence of events, which would be related to the other SP's

    Thanks for the suggestion.  Still nothing.  All other SP run but that problem one just hangs.  It has been over 30 minutes and still in process mode.  The SP is super fast so it should be done in under 1 minute max.

    I have to make sure that rolllback works so I used Answer 2 from this post:

    https://dba.stackexchange.com/questions/318766/guaranteed-rollback-in-ssis

    The rollback logic works great.

    Any other suggestions?  I am open to all ideas.

    Thank you

  • Try running the problem proc first (in a SEQ container) before all of the others.


Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply