Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can someone please point me in the right direction. I keep getting the below error message when I try to run a job that executes a package I created and saved on the SQL Server.

12/03/2012 17:32:15,New_App_R,Error,1,SQLAAG01,New_App_R,New_App_R,,
Executed as user: NT Service\SQLSERVERAGENT.
Started:  5:32:15 PM
Could not load package "\MSDB\New_App_R" because of error 0xC00160AE.
Description: Connecting to the Integration Services service on the computer "SQLAAG01" 
failed with the following error: "Access is denied." By default only administrators 
have access to the Integration Services service. On Windows Vista and later the process 
must be running with administrative privileges in order to connect to the Integration 
Services service. 

I also tried creating the same package and saved it on the file system (desktop) and when I try to execute the package using the below code in a job or in a query window

EXEC @returncode = xp_cmdshell 'dtexec /f "C:\Users\jvelez\Desktop\New_App.dtsx"'

I get another access denied error that says:

Description: Failed to open package file "C:\Users\jvelez\Desktop\New_App.dtsx"
due to error 0x80070005 "Access is denied.".  This occurs when loading a package
and the file cannot be opened or loaded correctly into the XML document. This can
be the result of specifying an incorrect file name when calling LoadPackage or the
specified XML file has an incorrect format.

If I double click the package on my desktop it runs and works perfectly, but what I need to do is schedule the package to run. I tried creating jobs in different ways and adding myself to the administrators group but nothing seems to work. Any advice would be greatly appreciated.

I'm running SQL Server 2012 on Windows Server 2008

share|improve this question
2  
"Executed as user: NT Service\SQLSERVERAGENT." -- this service account needs to be granted permissions to access the package. –  Jon Seigel Dec 4 '12 at 17:47
1  
That wouldn't explain why he got an access denied error while running it manually so there may be something else going on as well. Have you checked what the package does and confirmed that all files and permissions are correct for the account running the package? –  cfradenburg Dec 4 '12 at 17:55
1  
@cfradenburg: xp_cmdshell runs as the database engine service account (for sysadmins), which presumably doesn't have access either. –  Jon Seigel Dec 4 '12 at 18:04
1  
The reason why the xp_cmdshell failed could be due to the fact that you're connecting to SQL as a login in the sysadmin group, which will run xp_cmdshell as the SQL service account. This serivce account may not have rights to the location of the dtsx package. –  Keith Tate Dec 4 '12 at 18:05
    
@KeithTate you were absolutely right. I was connecting to SQL as a login in the sysadmin group. I gave the rights to the service account to the actual package file and it worked great. Thanks! Only problem I have now is that No matter what privileges and rights (administrator and sysadmin) I give to the SQLSERVERAGENT and the MSSQLSERVER accounts I still can't run the package on the SQL Server (same package but on server). I am trying to run the package from a Step in a JOB. I put some more details in my replay to your answer below. –  JuanVelez Dec 5 '12 at 3:18

1 Answer 1

up vote 1 down vote accepted

The 'Service\SQLSERVERAGENT' service account needs access to the file system to run that package.

You can also setup a proxy for the SSIS package and run the job using that proxy account.

share|improve this answer
    
Thank I was able to finally get the Package in the file system (desktop) to workby adding Full control rights to NT SERVICE\SQLSERVERAGENT and NT SERVICE\MSSQLSERVER to the actual package file BUT I have not been able to run the Package from the SQL Server. I added both NT Service accounts as administrators on the server as well as in the sysadmin roles in SSMS. I still get the "Access is denied." By default only administrators have access to the Integration Services service" message. Any suggestions??? Im running Windows Server 2008 R2 enterprise and trying to run package from job step. –  JuanVelez Dec 5 '12 at 3:19
    
I'm going to try and set up a Proxy account now to run the job. If that works then I'll be good to go. –  JuanVelez Dec 5 '12 at 14:37
3  
This answer could be improved with additional detail and links to resources. –  Paul White Dec 5 '12 at 16:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.