June 21, 2011 at 2:57 am
Ok Ok I know you think this has been asked a million times before but just hear me out?
The SQL Server Agent Account runs under a domain account which has permission on a network share. I have a package which reads in CSV files from this share. I can log onto the server as the SQL Server Agent Account and run the package (from the parent job onwards) in BIDS no problemo.
As soon as I try to run the same package (in both 32 or 64 bit mode) this as a job it fails with this error "Description: The file name property is not valid. The file name is a device or contains invalid characters."
What is going on??? I can access the same network share on this server when logged in as the agent account. This is a new one to me. Even making the account a domain admin as a test does not solve the issue. It must be something specific to the SQL Server Agent and how it executes jobs.
It may be worth nothing that I've had this working in the same way on a different server for the same client.
Any advice welcome!
June 21, 2011 at 3:20 am
What is the full UNC path + file name?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 21, 2011 at 3:23 am
Here's an example of one of them
\\bhdata1\eig\Central\QMS\Archive\qlextlivi4.csv
June 21, 2011 at 3:30 am
How are you passing the file names to the job? Is it hard-coded, or via some sort of config?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 21, 2011 at 3:36 am
Yes, from a SQL Server Configuration table.
June 21, 2011 at 3:48 am
Everything sounds fine.
Notwithstanding your comments, I think the next step should still be a check on permissions. Please try putting the file in
c:\import
(or wherever)
and see whether you still get the error.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 21, 2011 at 3:50 am
Yep, I've done that too.
I copied the files onto the server, update the connection and it worked from the job fine. It just will not hit a UNC path from the job.
I am at a loss.
June 21, 2011 at 5:13 am
Ok listen to this. If I set up a proxy account a link it to the SQL Server agent domain account it works?? Something is not quite right when the SQL Server agent account is used and it makes the leap from the local machine and onto the network
June 21, 2011 at 5:46 am
aaa-322853 (6/21/2011)
Ok listen to this. If I set up a proxy account a link it to the SQL Server agent domain account it works?? Something is not quite right when the SQL Server agent account is used and it makes the leap from the local machine and onto the network
Sounds bizarre, but I'll bet that you're happy to take that solution - not too much of a hack.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 21, 2011 at 5:59 am
No so happy as this is in the UAT environment, it somehow worked ok in the dev environment and who knows what might happen in production!
I will continue to work to get to the bottom of it and post a resolution on here (if I get one!)
June 21, 2011 at 3:45 pm
Strange.
Any difference if you move higher up in the path?
\\bhdata1\qlextlivi4.csv
Any shares in the path?
June 30, 2011 at 8:51 pm
It's been a while, but I ran in to the same issue - the service account had full rights to the folder, but couldn't access the file. After digging for far too long, I found that one of the folders in the directory structure was inaccessible by the service account. Example:
\\Server\Folder1\Folder2\Folder3\File.txt
In my case, the root folder (Folder3) allowed the service account full permissions, but one of the uplevel folders did not. When I granted the service account access to that parent folder, everything worked as expected.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
June 30, 2011 at 11:29 pm
That is good to know. Thanks for posting a solution Tim.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply