Problems with SQL Server Agent

Question

Post reply

Problems with SQL Server Agent

stormsentinelcammy

SSCrazy

Points: 2493
More actions
May 9, 2011 at 9:40 am

#235986

Hi All,
Below is the error that I am getting when trying to run a package from the SQL Server Agent, I am calling the .dtsx package from File System and I have all rights to do anything and everything (sysadmin) and the SQL Server Agent is running as Local System on the server. Any suggestions of what this might be?
Message
Executed as user: HSA\TEST. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:12:35 AM Error: 2011-05-09 11:12:36.27 Code: 0xC0011007 Source: {17AECDD6-26A8-422D-B188-607A3391B928} Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. End Error Error: 2011-05-09 11:12:36.27 Code: 0xC0011002 Source: {17AECDD6-26A8-422D-B188-607A3391B928} Description: Failed to open package file "C:\ORG\TEST.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. End Error Could not load package "C:\ORG\TEST.dtsx" because of error 0xC0011002. Description: Failed to open package file "C:\ORG\TEST.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. Source: {17AECDD6-26A8-422D-B188-607A3391B928} Started: 11:12:35 AM Finished: 11:12:36 AM Elapsed: 0.437 seconds. Process Exit Code 4. The step failed.

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

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Sounds like a permissions issue.

Can you log on using the SQL Server Agent Service Account and run the SSIS Package interactively?

Also try to navigate to the Path of the File using Explorer.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

stormsentinelcammy SSCrazy Points: 2493 More actions · Answer 2

Thanks for the help welsh, I'll give it a try now and see what I get. I do set the "Run As" to "SQL Server Agent Account Service", but when I set it to that it gives me that error, I have already tried creating a proxy and creating a credential and still no luck. I think its a permissions issue but I want to exhaust all of the permission possibilities before I say its a permissions issue. Any other suggestions in the meantime?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Would it be possible to RDP into the Server and Log on using the Service Account.

Could you try it and rule out the permissions?

What account do you use when you not running the package as a job?

Please try this first. If you are not an Admin on the Server try grabbing someone that is.

I assume that you can run the package other than the Agent Account and that the file exist?

Regards,

Welsh

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

stormsentinelcammy SSCrazy Points: 2493 More actions · Answer 4

I am sysadmin on the Server, so I have SA privileges to everything. I do log into the server that runs the job and run the job manually and through the execute package utility and the job does exactly what I want it to do. Is there a way that I can have the execute package utility run the job for me through the SQL Server Agent? If so maybe that can help out, but I still don't know what is going on with the permissions when I have looked over everything many times already, maybe I missed something along the way any suggestions?

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I asked if you could log on using the SQL Server Agent Credentials and perform the same actions that you did using your admin account.

Do not logon as yourself, log on using the SQL Server Agent Service Account and go from there.

I have run into this problem before.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 6

Give SQL Server Agent service account rights to access the particular file on the Server

My Blog
www.aureus-salah.com

stormsentinelcammy SSCrazy Points: 2493 More actions · Answer 7

Hey welsh and syed, apologies for the late reply, but can you guys guide me as to the process to do this? I'm not sure how to do that, I've never really tried that. You know the the typical i.e. "Right click on my computer and select manage" something like that so that I can make sure I follow your methodology to the T, because like you said welsh, you have dealt with this before and I have a feeling that I haven't tried your method yet. Thanks in advance.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

Try the following URLs:

http://www.youtube.com/watch?v=VOaFbRFsqxs

http://technet.microsoft.com/en-us/library/cc754178.aspx

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

stormsentinelcammy SSCrazy Points: 2493 More actions · Answer 9

Thanks welsh, I was waiting to hear back from you. I will try both links and get back to you in a bit with the results. Thanks again welsh.

stormsentinelcammy SSCrazy Points: 2493 More actions · Answer 10

I'm still getting the same error even after setting the appropriate permissions with the video link you sent me. I have attached a picture to show the error that I get when I'm trying to run the package through execute package utility. I have also attached another picture with the screen of the command line and how it looks.

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 11

That error is the same one that you would get if any user typed a path to a file server and tried to run a file they didn't have permissions to open.

The account running the package, not your account, but the SQL Agent or proxy account, need permission to the path and file shown in the error.

This is set in Windows, but a Windows administrator by adding the user to a group that has permissions to that path.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

stormsentinelcammy,

Do you have a Network Admin that could help you check on the permissions?

Regards,

Welsh

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 13

Just because I always like to check the easy answers first, is C:\ORG\TEST.dtsx actually located on the server, or is it on your machine? One of the easiest mistakes to make in cases like this (and I've made it more than my fair share) is to forget that when the job runs, its the agent running it, and its local to the server. Any drives, paths etc will be run as if you are logged into the server. However when you set up the job, if you were setting it up from your machine, you would be seeing your local drives.

Kenneth

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

stormsentinelcammy SSCrazy Points: 2493 More actions · Answer 14

Yes, I do have a network admin that could help me on permissions. You think that the network admin has to be involved with this issue even though I'm the Admin on the server?