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