0

I have an SSIS package, that worked just fine for months. It was planned in an SQL Agent Job, and launched every day, as the 8 step of the 23-steps job.

Then all out of nowhere (understand, with absolutely no changes), the said package/step, started returning a failure. But where it start becoming kind of weird, is that :

  • Launching the package in stand alone (from Visual Studuo or Execute Package Utility) is running as smooth as it can be.
  • Starting the SSA job from step 8, also return success.
  • Starting the SSA job from step 1, return a failure at step 8.

Thanks in advance for any idea you might have.

Enauofas
  • 35
  • 1
  • 1
  • 6
  • Whats the error that it fails with? – TMNT2014 Dec 05 '14 at 15:31
  • The logs aren't returning any messages ... it simply shows a "Failure" on the agent. Also, when I check the message returned in the agent, it is incomplete, but the visible part is similar to the message returned when the job success with a "step failed" at the end instead of the usual success message. Guess it's worth mentioning that I can't modify the job to save longer messages since this is happening on a live server (and not reproducible on the test machine) – Enauofas Dec 05 '14 at 16:16
  • if you run it from step 7 does it still return success? – TMNT2014 Dec 05 '14 at 19:10
  • If you disable step 8 and run the job, does it still return success? – Tab Alleman Dec 05 '14 at 19:37
  • @TMNT No, it returns failure – Enauofas Dec 07 '14 at 14:06
  • @TabAlleman Yes it does return success if I disable step 8 thanks for you time – Enauofas Dec 07 '14 at 14:08
  • Here is where I am with this issue up until now : - It's is not a credential/Access Right problem - It's not a disk space problem - It's not a problem with the insertion method used – Enauofas Dec 09 '14 at 10:31

1 Answers1

0

I solved (temporarily) the problem by editing the @retry_attempts propriety of the jobstep in SQL Server Agent. I've set it to 1 so it will repeat one time if there is a failure. So after the test, there is still a failure on the first execution of the package, but the repeat works fine, which allow the job to continue "journey" without interrupting.

PS: It's definitely not a good, or even a satisfactory solution, mainly because I still don't know what the cause is. If you have any trails I can follow, please do not hesitate.

Enauofas
  • 35
  • 1
  • 1
  • 6