Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SSIS package run by SQL Agent fails

Posted on 2014-11-12
5
Medium Priority
?
778 Views
Last Modified: 2016-02-11
We have a package in dtsx format setup to automate some tasks over FTP.
When we run the package manually from Development Studio it works fine.
When we ask SQL Server Agent to execute the job it gives an error indicating access rights issue as follows:

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: 2014-11-12 16:38:07.48     Code: 0xC0011002     Source: {C439F4ED-1F2D-48BF-9DF8-5C895594B508}      Description: Failed to open package file "\\cphebtda01\dataanalyse$\Kunder\PM\SSIS Automation\StoreReportV2\Integration Services Project1\Package.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.  End Error  Could not load package "\\cphebtda01\dataanalyse$\Kunder\PM\SSIS Automation\StoreReportV2\Integration Services Project1\Package.dtsx" because of error 0xC0011002.  Description: Failed to open package file "\\cphebtda01\dataanalyse$\Kunder\PM\SSIS Automation\StoreReportV2\Integration Services Project1\Package.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.  

SQL Server and dtsx resides on the same server.

When we look at the folder permissions for the folder that contains the dtsx how can we verify that the NT Service\SQLSERVERAGENT has the necessary rights to the folder?

No Folder rights have been changed since server install (is app. 2 months old).
0
Comment
Question by:Panthom
  • 2
  • 2
5 Comments
 
LVL 84

Expert Comment

by:David Johnson, CD, MVP
ID: 40437828
Use the account that in services that sql agent runs under.
0
 
LVL 6

Expert Comment

by:Mandeep Singh
ID: 40439152
hi,

do one thing run it as proxy.

restart your sql services and then give a try.
0
 

Author Comment

by:Panthom
ID: 40439751
Hi there

I think we may be looking at this the wrong way - since SQL Server agent runs as an NT Service - does that not implicitly grant it access to all files on the NTFS volume via the SYSTEM Group?

If the above is true and NT Service\SQLSERVERAGENT is a member of SYSTEM then the error must be something else than the services access to the file - yes??

Best Regards
Thomas
0
 
LVL 84

Accepted Solution

by:
David Johnson, CD, MVP earned 1500 total points
ID: 40440045
does computername$ have access to that share in the ntfs file permissions also check your share permissions
0
 

Author Comment

by:Panthom
ID: 40722774
The Account we where trying to use had access to the Root drive d:\$ - but it did not work untill we specifically gave the account access to the folder that contained the SSIS package to execute.

David Johnson's remark helped solve this issue eventually - just took us a long time to find the way to fix it with this info - due to wrong assumptions :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question