October 29, 2008 at 9:21 am
Good Afternoon,
I've seen a few posts on various forums regarding SSIS packages that are executed in SQL Agent Jobs through SSMS and how they indefinitely 'hang' when they get to the 'executing' stage. But not a lot of resolutions to this problem.
I've now deployed one of the SSIS packages i've developed onto our Development server to test that it can be scheduled and run automatically and I am now running into the same problem. Does anybody know what is to cause this hanging? I don't understand why it hangs when:
1.) It runs fine in Visual Studio
2.) It runs fine through the file system when executed in that fashion
I've checked simple things such as that the MS SQL Agent is running and that there is enough memory free to be running the package but to no avail.
To give a bit of information on what the SSIS Package is doing:
1.) Execute SQL task to obtain a maximum id value
2.) Using a script task to obtain a stream of XML data to an XML File on one of the network drives
3.) Manipulate the XML File to remove the reference .DTD at the top
4.) Import the XML data into a SQL 2005 on the Development server
Is packages hanging when executed in a job a known problem with SQL2005? Are there known problems that anybody can shed any light on?
Any information would be much appreciated!
Many thanks,
Dan
July 12, 2010 at 6:06 am
Does anyone have any suggestions as to how this issue can be resolved?
I am experiencing the same problem and, whilst there seems to be plenty of people experiencing the problem, i have not been able to find any solutions.
Any help would be greatly appreciated.
July 12, 2010 at 8:46 am
The usual suspect in these cases is the permissions of the Windows account under which the job is executing. When you run the package interactively, it is executing with your login permissions. When you run it via SQL Agent job, it is running in the context of the login for the SQL Agent windows service.
One way to troubleshoot the issue is to log onto the server console using the same account that the SQL Agent uses. Then run your package interactively. You may then be able to see what condition is causing the job to hang.
For example, I had a similar situation. When I ran the job as I suggested above, I discovered that a software component was displaying a one-time license prompt that was not being handled.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply