Trouble with mapped network drive while running package through SQL Server Agent

Hi Experts,

I have developed a package using script tasks to refresh data in Excel spreadsheets. In this Excel spreadsheets there's some code as well, when the sheet is opened it refreshes data from a mapped network drive. All of this works fine when I run this in SSIS, but when I export the package to SQL Server and try to run it as a Job it fails, telling me that it can not locate my mapped drive (W:).

Anyway to overcome this problem, so my sheets can be updated from the network drive will be greatfull.

Thanks
LVL 1
cErasmusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mfhorizonCommented:
0
lacrewgaCommented:
Are your users being mapped via login.bat or similiar? Do users have individual mapping set up on their computer? Can you see the drive in Windows Explorer at the time of error?
0
mfhorizonCommented:
Use the UNC path
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mfhorizonCommented:
For details see this solution that discusses DTS Packages and Map drives.
http://www.windows-tech.info/15/478bea7627a52af7.php

good luck
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cErasmusAuthor Commented:
Hi MFHorizon,

I tried to change my connection in Excel to a UNC path, but now I get the error telling me that the file is already open or I don't have permission to read or write to the file, this was ran through Sql Server agent. But it works in SSIS....

0
Anthony PerkinsCommented:
>>I don't have permission to read or write to the file<<
The problem is that the account for the SQL Server Agent service does not have sufficient permissions to access the files.  More than likely it is a Local System Account.  You will have to change it to a Domain Account.  Also, and as suggested, use UNC and not mapped drives.
0
cErasmusAuthor Commented:
Won't this cause problems for my other jobs if I change from the Built-In account to a specific account. I just want to make sure, this changes is done in the Sql Server Configuration Manager?
0
Anthony PerkinsCommented:
>>Won't this cause problems for my other jobs if I change from the Built-In account to a specific account. <<
It is not a "Built-In" account.  It is the default account used when installing SQL Server.  There is nothing inherently wrong with changing it to some other more appropriate account, but you need to discuss that with your IT dept so that all security related problems are covered.

>>I just want to make sure, this changes is done in the Sql Server Configuration Manager?<<
If you need to schedule a job via SQL Server Agent or any other Windows service, then that service will need the appropriate permissions.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.