Agent job containing SSIS Package step runs forever but doesn't do anything.

Question

Agent job containing SSIS Package step runs forever but doesn't do anything.

DBANewbie

Hall of Fame

Points: 3091
More actions
August 25, 2010 at 7:18 am

#148546

I've seen a few reports of similar problems to this but none of the posted solutions seem to help with this particular set-up.
I have a SQL Server which has a dozen or SQL Agent jobs which run each night to transfer in data from various external sources (usually CSV files).
These have been working fine for months but now all of a sudden the jobs are not completing; they just seem to run and run without making any progress. This is only happening on those jobs which use SSIS packages; we have others which simply perform operations within the databases and these work fine.
I have set up a new SSIS package myself which simply creates a table in one of my databases and whilst it runs if i execute the task from within BIDS; as soon as i include it in an agent job and try to run that job it just execute forever without making any progress. I eventually have to stop the job.
There is nothing in the error log to identify any problems and the account that the agent service run under has the neccessary permissions to perform all actions required (like i said it has worked in the past with the same account).
I'm at a dead end and would really appreciate any help you can provide.
Regards,
JODMAN.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 1

Have you tried storing the packages on the server/Pointing the SQL Job to the file system (whichever of the 2 you haven't tried).

Also, if you stick logging on for the SSIS package, does it actually add ANYTHING to the SSIS job log when you run via Agent?

DBANewbie Hall of Fame Points: 3091 More actions · Answer 2

Thanks for the response.

I have tried both methods and neither is working; although if i store the SSIS package on the server and execute it via SSIS withiin management studio the package executes successfully.

I also enabled logging to try and identify if any errors but NOTHING is being written to the log.

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 3

As pointless as it sounds, if you add in a normal T-SQL step before the SSIS step and then run the package, does it run the first step ok? or does SSIS being involved just kill the entire job?

DBANewbie Hall of Fame Points: 3091 More actions · Answer 4

I set up a TSQL step to simply create an empty table before running the SSIS step as you suggested and the first step executed without a problem (the new table was created).

So the problem appears to be exclusively with executing SSIS packages.

Thanks for your help so far.

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 5

I'm tempted to think that the SQL Agent service account you are running the job under is lacking the permissions to both the file system location of the SSIS, or to access the SSIS Server when running it as a SQL Stored package. However - I'd have expected an error rather than an endless job. I'll get back to you if I think of anything.

EDIT: Have you had any hotfixes applied lately? Have you tried running the package using dtexec?

DBANewbie Hall of Fame Points: 3091 More actions · Answer 6

I'll give it a try with DTEXEC and let you know the outcome.

There have been no hotfixes applied in the weeks leading up to the first occurance of this problem.

Thanks for your help thus far, any further help you or anyone can provide would be greatly appreciated.

DBANewbie Hall of Fame Points: 3091 More actions · Answer 7

The job runs successfully using the DTEXEC utility; so it's just if i try to run the job through the SQL server agent; does this confirm your suspicions that the account under which the Agent Service is running does not have sufficient permissions?

DBANewbie Hall of Fame Points: 3091 More actions · Answer 8

Just to complete the picture here; we performed a full server reboot out of hours and the SQL Server Agent Jobs that contain SSIS package steps are now executing without a problem.

This isn't the first time that this has happened so i still would like to know what is happening to cause the problems but for now i can simply set up an alert to let me know when the problem occurrs and then reboot the server myself.

Any further advice / thoughts would be most welcome.

Shark Energy SSCrazy Eights Points: 8389 More actions · Answer 9

No errors in the Agent logs?

What user credentials did you use when running dtexec?

If/when it goes again, until you can find time for the out of hours reboot you could run the Jobs as DTEXEC statements. But hopefully a reason will be found before then!

nick.mcdermaid Hall of Fame Points: 3809 More actions · Answer 10

I have this exact issue. It appears that the job starts but never starts the package and never finishes.

A restart of the SQL Agent usually fixes it but not this time!

prateekg.garg Grasshopper Points: 18 More actions · Answer 11

If the password is changed for the account with which SQL Server and Agent is running then it requires a restart of SQL Services. Not sure if this scenario applies in your case and causing the issue.