Error in SQL2008 job containing SSIS packet

Question

Error in SQL2008 job containing SSIS packet

mcaster

Say Hey Kid

Points: 662
More actions
October 13, 2010 at 11:17 am

#141007

The problem is this: I created a packet in SSIS logged on as 'DBAdministrator' which moves 2 directories of files from the local server to a remote backup server. The packet executes properly from within SSIS. When I include the packet as a step in a SSAgent job in Management Studios (again logged on as 'DBAdministrator'), the job fails with the following error message:
---------------------------------------------------
Description: Failed to open package file "C:\Documents and Settings\Margaret Casterlin\My Documents\Visual Studio 2008\Projects\DNN Backups\DNN Backups\MoveDNNFiles.dtsx" due to error 0x80070005 "Access is denied.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
End Error
-------------------
I also get the following message:
----------------------------------
Message
Executed as user: NT AUTHORITY\NETWORK SERVICE. The step failed.
-----------------------------------
The file name displayed is indeed the correct file name. However, we do not have domain controlers in this company and thus all the work must be done in mixed mode with SQL Server Authorization.
My question is: is this an access problem or an XML problem with the package?
If it is access, is there a known way to work around it?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

It looks like the package you are trying to execute is in specific user sub-folder and the "NETWORK SERVICE" account doesn't have permissions to this folder. Try moving the package to a folder or location where it could be accessed by the "NETWORK SERVICE" account.

Another option is to setup your SQL Job Step to execute under a proxy account, where the proxy account has the required permissions.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

mcaster Say Hey Kid Points: 662 More actions · Answer 2

CosyROC gave good advice and after allowing NETWORK SERVICE access to the folder with the SSIS package, the job works just fine.

Thanks much, CosyROC!

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Glad you got it working, but the "My Documents" folder is a very poor choice for a folder to use, as you should have noticed.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

mcaster Say Hey Kid Points: 662 More actions · Answer 4

Being a reasonably new 'accidental' DBA, I'm open for all the good advice I can get. My Documents seems to be where SSIS wanted to put stuff, so I went with the flow. What's your sage advice?

Phil Parkin SSC Guru Points: 246965 More actions · Answer 5

mcaster (10/13/2010)
Being a reasonably new 'accidental' DBA, I'm open for all the good advice I can get. My Documents seems to be where SSIS wanted to put stuff, so I went with the flow. What's your sage advice?

Somewhere that does not vary depending on who is logged in - almost anywhere else!

Suggest you use a UNC path to an appropriately shared folder somewhere on a data (rather than system) drive.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin