Solved

SSIS package run by SQL Agent fails

Posted on 2014-11-12
5
485 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 81

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 81

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sorting a SQL script 5 42
DFSRPrivate folder missing 2 18
Delete duplicates from SQL Server table 2 27
Point DNS (IP) to Azure VM not working 23 45
OfficeMate Freezes on login or does not load after login credentials are input.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how the fundamental information of how to create a table.
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…

752 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