Bluefusion
asked on
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
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
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
how/where do you specify this connection string?
this looks like you have a quoting issue...
"Provider=Microsoft.Jet.OL
this looks like you have a quoting issue...
"Provider=Microsoft.Jet.OL
ASKER
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.
ASKER
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!
ASKER
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.OLE
Where SERVERNAME is the name of the network path server.
(its actually an Excel file in this case)