Share via

SSIS Task Just won't run via SQL Agent

  • Article

Question

Wednesday, September 21, 2011 11:32 AM

I have an SSIS Package which runs fine via DTEXEC. Now I am trying to execute it via SQL Agent.

The error which I get is

 

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
09/21/2011 07:27:47,AM_SP2010_Job,Error,0,ETC784994,AM_SP2010_Job,(Job outcome),,The job failed.  The Job was invoked by User DOMAIN\user.  The last step to run was step 1 (AM_SP2010_JobSSIS).,00:00:00,0,0,,,,0
09/21/2011 07:27:47,AM_SP2010_Job,Error,1,ETC784994,AM_SP2010_Job,AM_SP2010_JobSSIS,,Executed as user: Domain\User2. The process could not be created for step 1 of job 0x353775A3A4D1964A8FB606E47A12653C (reason: A required privilege is not held by the client).  The step failed.,00:00:00,0,0,,,,0

Note that both Domain\user and Domain\User2 are members of Windows Adminstrators group and SQL sysadmin group.

Infact SQL Agent Service itself is running as Domain\User2

All replies (7)

Wednesday, September 21, 2011 5:58 PM âś…Answered

funny. i tried everything on the web but nothing worked (including reboot).

then I just deleted then recreated it with a different name and it started to work.

Here is all the steps which I did

1) Made the account as Local administrator

2) Made the account as member of SQL sysadmin

3) INside of MSDB made member of SQLAgentUser role

4) Inside of MDB made member of db_ssisadmin, db_ssisoperator

5) Made member of SQLServerSQLAgentUser$Machine$MSSQLServer

6) Made member of SQLServerMSSSQLUser$Machine$MSSQLServer

 

After that rebooted the machine.

After that deleted the job and then recreated it.

 

Wednesday, September 21, 2011 11:59 AM

What happens if you youeself login as Domain\User2 and execute the package using DTEXEC?Tibor Karaszi, SQL Server MVP | web | blog

Wednesday, September 21, 2011 1:24 PM

yes. I ran the package using Domain\User2 and it ran successfully.

Wednesday, September 21, 2011 4:38 PM

Did you change service accoun for Agent? IF so, did you do it using SQL Server Configuration Manager? If no, do so (doing it from Windows Services applet doesn't cut it).Tibor Karaszi, SQL Server MVP | web | blog

Wednesday, September 21, 2011 4:53 PM

always use SQL Server Configuration manager for change of service accounts.

Monday, October 10, 2011 4:33 PM

Thank you for posting your solution. I was in the same predicament until I did what you did - delete and then recreate the job. Now, SQL Server Agent can run my SSIS package, which accesses the network file system.

I am using SQL Server 2005. Obviously, there is a bug. Microsoft, please fix this in a patch.

Wednesday, August 3, 2016 9:21 AM

Hi

Can you tell me how to do the above steps?