January 31, 2025 at 8:59 pm
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
January 31, 2025 at 10:17 pm
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
January 31, 2025 at 11:12 pm
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
February 2, 2025 at 11:00 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy