Solved

SSIS package run by SQL Agent fails

Posted on 2014-11-12
5
327 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 78

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 78

Accepted Solution

by:
David Johnson, CD, MVP earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now