Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSIS package run by SQL Agent fails

Posted on 2014-11-12
5
Medium Priority
?
684 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 83

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 83

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

718 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