SQLAgent won't complete jobs that require network connections

Posted on 2007-09-28
Medium Priority
Last Modified: 2011-10-03
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.

Question by:Bluefusion
  • 3
  • 2
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19981782
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)

Author Comment

ID: 19982197
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)
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19983314
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""; "

Author Comment

ID: 19985360
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.

Author Comment

ID: 19992332
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!

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question