SQL Server Agent : Job Failure

Question

SQL Server Agent : Job Failure

elhelalaoui

Right there with Babe

Points: 766
More actions
March 23, 2014 at 6:37 am

#305570

Hello,
I wish to put the result of a query in Excel then send it to a number of persons at a specified moment. I've started by the configuration of all this on a SSIS package, till then everything is all right, all steps are going well. The problem appears when I tri to schedule this work via the SQL Server Agent.. You'll find below the steps i've done :
1- On the general part, I name the job and put the account sa as owner.
2- On the Steps part, i create a new step and then I set :
2.1 the name of the step.
2.2 Run AS = SQL Agent Service Account
2.3 On the general tab, I put the source of the package as a System File, I browse the package, then i save all.
When I tri to execute the Job, I receive this error (On job history) : "The JOB Failed. The Job was Invoked by user NomDuServeur\Administrateur. The last step to run was Step 1." ==> "Executed as user : MonDomaine\Système. The package executon failed. The step failed." 😐
FYI : I'm using SQL Server 2005 with the OS Windows Server 2008 R2 64bit.
Could you help please ?
Thanks and regards.

Viewing 15 posts - 1 through 15 (of 22 total)

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

There are two approach:

one is you can use notification feature of the job to send e-mail notification when job completes i.e. success or failure.

Second is you periodically check the job history table in MSDB database to send result via e-mail attachment.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

elhelalaoui Right there with Babe Points: 766 More actions · Answer 2

Thanks free_mascot.

The problem is that all my jobs never succeed :crying:

Do you have any idea about that error ? 🙂

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

Check the permission. Is there any permission issue? All jobs are failing with same error?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

Create a credential with your ID and a SSIS agent proxy with that credential. Then run the job using that credential.

http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

--

SQLBuddy

elhelalaoui Right there with Babe Points: 766 More actions · Answer 5

Thanks free_mascot, thanks sqlbuddy123. All my jobs are going with the same error.

I've just tried the solution of the proxy, and getting this error now : Could not get proxy data for proxy_id = 10 :exclamationmark:

Please help :crying: I'm executing all my packages manually everyday :sick:

Thanks 🙂

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

elhelalaoui (3/26/2014)
Thanks free_mascot, thanks sqlbuddy123. All my jobs are going with the same error.
I've just tried the solution of the proxy, and getting this error now : Could not get proxy data for proxy_id = 10 :exclamationmark:
Please help :crying: I'm executing all my packages manually everyday :sick:
Thanks 🙂

Change the job owner to SQL Agent Service account or your ID and run it. Lets try that ..

--

SQLBuddy

elhelalaoui Right there with Babe Points: 766 More actions · Answer 7

It Doesn't working, I'm getting the first error now 🙁

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 8

elhelalaoui (3/27/2014)
It Doesn't working, I'm getting the first error now 🙁

Did you run it like this ...

Change the job owner to SQL Agent Service account or your ID and run the job step as SSIS Proxy that you created before.

--

SQLBuddy

elhelalaoui Right there with Babe Points: 766 More actions · Answer 9

I'm still having this error : Could not get proxy data for proxy_id = 11

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 10

elhelalaoui (3/30/2014)
I'm still having this error : Could not get proxy data for proxy_id = 11

When creating the credential were you using DOMAIN\USERNAME or just USERNAME ?

--

SQLBuddy

elhelalaoui Right there with Babe Points: 766 More actions · Answer 11

I used TheNameOfTheComputer\Administrateur

Thanks sqlbuddy123 for your support

I'm in waiting 🙂

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 12

elhelalaoui (3/31/2014)
I used TheNameOfTheComputer\Administrateur
Thanks sqlbuddy123 for your support
I'm in waiting 🙂

You are welcome, elhelaloui. Could you import the package into MSDB, select the SSIS package in the job step and then execute ?

Also please post the error message that you see in the job history ..

--

SQLBuddy

elhelalaoui Right there with Babe Points: 766 More actions · Answer 13

I've imported the package onto MSDB and here what i'm having now in both cases :

Case 1 : Running the step with the proxy :

errors ==> Job outcome : The job failed. The Job was invoked by User ServerName\Administrateur. The last step to run was step 1 (Test_Proxy2). Test_Proxy2 : Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 11). The step failed.

Case 2 : Using SQL Agent Service Account :

errors ==> Job Outcome : The job failed. The Job was invoked by User ServerName\Administrateur. The last step to run was step 1 (Test_Proxy2). Test_Proxy2 : Executed as user: DomaineName\Système. The package execution failed. The step failed.

| Test_Proxy2 is the name that I gave to the step. |

Thanks 🙂

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 14

elhelalaoui (4/2/2014)
I've imported the package onto MSDB and here what i'm having now in both cases :
Case 1 : Running the step with the proxy :
errors ==> Job outcome : The job failed. The Job was invoked by User ServerName\Administrateur. The last step to run was step 1 (Test_Proxy2). Test_Proxy2 : Unable to start execution of step 1 (reason: Could not get proxy data for proxy_id = 11). The step failed.
Case 2 : Using SQL Agent Service Account :
errors ==> Job Outcome : The job failed. The Job was invoked by User ServerName\Administrateur. The last step to run was step 1 (Test_Proxy2). Test_Proxy2 : Executed as user: DomaineName\Système. The package execution failed. The step failed.
| Test_Proxy2 is the name that I gave to the step. |
Thanks 🙂

Check 3 things :

1. If the proxy got created successfully.

2. Package protection level of the SSIS package.

3. Check if you see any error messages in the SQL Agent error log when the job was run.

--

SQLBuddy