SQLAgent won't complete jobs that require network connections

Posted on 2007-09-28
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
    LVL 142

    Accepted Solution

    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

    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 142

    Expert Comment

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

    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

    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

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how the fundamental information of how to create a table.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now