Problem with SQLAgent Running SSIS Package "...package execution failed..."

by **Stephen** » Fri, 17 Sep 2010 01:45:15

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent What am I missing

-Stephen

by **Gops Dwara** » Sun, 19 Sep 2010 02:46:16

Are you able to run the package successfully using DTExec If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

by **Question_Answe** » Mon, 20 Sep 2010 04:48:18

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

by **satees** » Tue, 21 Sep 2010 03:47:17

Delete the table you created with Import/Export utility and then run the job again. This worked for me.

by **odrc** » Wed, 22 Sep 2010 05:49:19

I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.

by **ervin** » Thu, 23 Sep 2010 07:51:21

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

by **Abha** » Fri, 24 Sep 2010 06:50:20

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

by **Abha** » Sat, 25 Sep 2010 08:52:22

Does your window account have sufficient rights If not try giving full rights to your account and try again.

by **REM760** » Sun, 26 Sep 2010 10:54:24

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

by **REM760** » Mon, 27 Sep 2010 09:53:23

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

by **DDvora** » Tue, 28 Sep 2010 11:55:25

I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.

by **Syndrak** » Wed, 29 Sep 2010 13:57:27

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege

Could this have to do with Kerberos delegation I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

by **morel** » Thu, 30 Sep 2010 12:56:26

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

by **-andy** » Fri, 01 Oct 2010 14:58:28

Hi! If you follow Abha's instructions from 03-01-2007 you can fix this problem! I finally managed to get it to work after months of trying to fix it. By selecting SQL Server Agent->Jobs->[the job] open it's properties and find the steps that do not work. Edit the step and change where it says "run as" to "JobProxy" or whatever else you've called in "ON EVERY PACKAGE THAT WILL NOT SUCCESSFULLY COMPLETE UNDER THE SYSTEM LOGIN". Save outta everything and run your Job. It should work! Note: I when i first tried it, i did not change all the packages to run under the new proxy and it failed.