SQLAgent won't complete jobs that require network connections

Hi everyone,

I have been developing a data warehouse with SQL Server 2005 Standard running on a Windows 2003 server. I have created several packages using SSIS and they run fine within Visual Studio and using the DTExec program. But when I try to schedule the package as a job, the job fails. I have looked over many postings about issues regarding permissions but have not found the answer. Only jobs that require network resources fail. Ones that don't run fine. I am currently using a proxy to run the jobs on a Windows Domain account that has all the permissions. This same account is the one I use to login to SQL Server Management Studio. The scripts run fine on there. The error I am getting says that the network data source path is invalid and does not exist when it actually does exist and works perfectly fine when run in Visual Studio. All the package does is extract information from an Access database on a networked drive.

Any insight on this problem would be much appreciated.

Thanks,
Bluefusion
BluefusionAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
is the "network path" a mapped drive?
if yes, that mapped drive will not be available from the job engine's thread.
you could only use unc path.

now, you also need the sql server agent service to run under a domain account (ie a domain account that has sufficient network access to that path)
0
 
BluefusionAuthor Commented:
Yes, the "network path" is a mapped drive. I have just tried using the UNC path and I'm getting the following error:

Executed as user: ELIA\administrator. Microsoft (R) SQL Server Execute Package Utility  Version 9.00.3042.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Argument "Excel" for option "connection" is not valid.  The command line parameters are invalid.  The step failed.

The UNC connection string I used for the file data source was:

Provider=Microsoft.Jet.OLEDB.4.0;DataSource=\\SERVERNAME\directory\Report\TrafficUpdate.xls;Extended Properties="EXCEL 8.0;HDR=YES";

Where SERVERNAME is the name of the network path server.

(its actually an Excel file in this case)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
how/where do you specify this connection string?
this looks like you have a quoting issue...

"Provider=Microsoft.Jet.OLEDB.4.0;DataSource=\\SERVERNAME\directory\Report\TrafficUpdate.xls;Extended Properties=""EXCEL 8.0;HDR=YES""; "
0
 
BluefusionAuthor Commented:
Its in the properties of the job step, under data sources. I've tried the quotes in various different ways but it still gives the same error.
0
 
BluefusionAuthor Commented:
It turns out I have to change the data source path in SSIS and not the job scheduler. Thanks so much for pointing me in the right direction!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.