Tuesday, March 20, 2012

Package could not be loaded - SQL Server Agent

I'm having an issue with executing an ssis package using SQL Server Agent. I have 7 packages that I've got 7 separate jobs for. I also have 7 separate stored procedures that each call the appropriate job using sp_start_job. We are using BizTalk to execute these 7 stored procedures, which in turn start a job, which in turn will call an SSIS Package to run. When we execute the 7 jobs spaced apart, i.e. 2 or 3 seconds apart, they all run fine. When we BizTalk executes them all at the same time, within milliseconds of each other, sometimes they all run fine, sometimes a few of the SSIS Packages end up with the error "The Package could not be loaded" in the SQL Server Agent history log.
Is there an issue when trying to execute multiple Packages all at the same time? It's not like I'm calling the same package 2 times, they are all different packages. Can anyone shed any light on this?
Thanks,
AndyI tried running 7 jobs each running differerent package, and everytime 1 failed with message package execution failed. With a second delay they passed always. I have opened a tracking bug for this.

There is workaround for this.

Set retry_attempts(also retry_interval using sp_update_jobstep) to any value other than default which is 0 .

Thanks for reporting this.

Thanks,
Gops Dwarak|||Thanks Gops. I was trying to avoid using the re-try attempts, but I guess for now I'll have to. Hopefully this will be fixed in SP1!

-Andy

No comments:

Post a Comment