June 6, 2012 at 1:23 pm
Hello,
I have an SSIS package that is hanging. The strange thing is that it runs fine when manaully run but when scheduled through the Agent it hangs in the middle.
Seems that SQL Profiler would be a great technology for capturing where and why it is hanging? Anyone have any advise about what events I should be tracing when creating that trace?
Thanks,
Sean
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
June 6, 2012 at 4:21 pm
The Replay Trace will capture everything. I would setup the package to use a unique Application Name in the connection string so I could filter on that in the Trace and ignore all other activity. I might also add some logging to the SSIS package so I could see where in the package it was getting hung up.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 7, 2012 at 12:03 am
What tasks do you use in the package?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 8, 2012 at 5:32 am
What accounts are used when you're running the package manually and through the agent? It seems like the agent is running with a different account and this account doesn't have the correct rights?
June 8, 2012 at 9:59 am
I didn't create the package and it is quite complex. Calls some 20 other packages from the main. Definitely think that permissions maybe at work here. The job runs under a proxy. I have created a profile trace that is going to capture alot of events (including deadlock chain events). Hoping that there is a deadlock event taking place to narrow down but that is probably wishful thinking.
Thanks everyone for you advise! I will let you know as I find things out.
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
June 8, 2012 at 10:47 am
If a deadlock were occurring I would expect the SSIS package to fail if it were chosen as the victim, or proceed if it survives the choice. How long does it hang before you give up and kill it?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 8, 2012 at 11:24 am
It can hang indefinitely. The job/package is being run during Europian hours. Two hours - or so - into it hangs. They kill it and then run it manually. Which seems to work everytime. The job is configured to run with a Proxy even if someone where to manually run the job it seems to me it still would be running as the proxy account
opc.three (6/8/2012)
If a deadlock were occurring I would expect the SSIS package to fail if it were chosen as the victim, or proceed if it survives the choice. How long does it hang before you give up and kill it?
A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
June 8, 2012 at 11:43 am
The package could be hanging up on some non-SQL task, so SQL Profiler may not be the solution.
Using SSIS logging sounds like a better idea. You could even throw in some FireInformation events of your own to help pin down where it hangs up.
June 8, 2012 at 12:19 pm
From your later posts it sounds like you're supporting another group remotely. If that is the case, or code changes are not an option to add logging, then might I recommend DTLoggedExec[/url]. It allows us to run existing packages and have them log information it would not normally log without modifying the package code.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply