Solved

SSIS package run by SQL Agent fails

Posted on 2014-11-12
5
276 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
Comment Utility
Use the account that in services that sql agent runs under.
0
 
LVL 6

Expert Comment

by:Mandeep Singh
Comment Utility
hi,

do one thing run it as proxy.

restart your sql services and then give a try.
0
 

Author Comment

by:Panthom
Comment Utility
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
Comment Utility
does computername$ have access to that share in the ntfs file permissions also check your share permissions
0
 

Author Comment

by:Panthom
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

The reason that corporations and businesses use Windows servers is because it supports custom modifications to adapt to the business and what it needs. Most individual users won’t need such powerful options. Here I’ll explain how you can enable Wind…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

771 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

14 Experts available now in Live!

Get 1:1 Help Now