none
SSIS Package Runs OK Manually But Not From SQL Server Agent...Permissions? RRS feed

  • Question

  • I have a problem where I have an SSIS package (SQL Server 2005) that won't run properly from SQL Server Agent, but it runs fine when kicked off manually from Integration Services -> Run Package or when run in debug from Visual Studio.

     

    The first step in the package checks for the existance of a file via a script task.  The script looks like this...

     

    Code Block

    Public Sub Main()

     

    Dim TaskResult As Integer

    Dim ImportFile As String = CStr(Dts.Variables("BaseDirectory").Value) + CStr(Dts.Variables("ImportDirectory").Value) + CStr(Dts.Variables("ImportFile").Value)

     

    If Dir(ImportFile) = "" Then

    Dts.TaskResult = Dts.Results.Failure

    Else

    Dts.TaskResult = Dts.Results.Success

    End If

     

    Return

     

    End Sub

     

     

     

     

    This script runs fine and the file is seen as expected when I run the package manually.  But as a step in a SQL Server Agent job, it doesn't see the file.

     

    The SQL Server Agent service is set to start up / log on as a Local System Account.  I've also tried setting up a credential / proxy (using an account that I know can see and even move / rename the file) to run the job as but that didn't seem to help.

     

    The package is being run from SQL Server (stored in MSDB) and is set to rely on SQL Server for sensitive information, so I don't think that's an issue; other packages are set up like this in terms of sensitive data and run fine.

     

    Any ideas why my script can't "see" the file I'm looking at when it's kicked off by SQL Server agent?  I've looked and looked...I can't seem to figure this out.  I would really appreciate any help you might be able to offer up.

    Wednesday, December 19, 2007 11:18 PM

Answers

  • If the variables are fine, then I think it is very likely that this is security related. Since the Agent is running under the local system account, have you verified that the local account can access the file? When you tried the proxy account, are you positive that it was set up properly, and that the account had the permissions to read the file?

     

    Another thing to check - is this a local file or is on another computer? If it is on another computer, make sure you are using a UNC path and not a mapped drive.

     

    Thursday, December 20, 2007 3:28 PM
    Moderator

All replies

  • Have you validated that the values in your variables are correct? You can do this by writing them to the log using Dts.Log() in the script task.

     

    Thursday, December 20, 2007 12:24 AM
    Moderator
  • Yes, the variables are correct.  The package will run fine when I run it manually.  The only change I make is to run the package via a SQL Server Agent job and it doesn't see the file.  I then run it immediately following manually and it sees the file just fine.  Puzzling!

    Thursday, December 20, 2007 3:21 PM
  • If the variables are fine, then I think it is very likely that this is security related. Since the Agent is running under the local system account, have you verified that the local account can access the file? When you tried the proxy account, are you positive that it was set up properly, and that the account had the permissions to read the file?

     

    Another thing to check - is this a local file or is on another computer? If it is on another computer, make sure you are using a UNC path and not a mapped drive.

     

    Thursday, December 20, 2007 3:28 PM
    Moderator
  • I'll verify that the proxy account can access the file, but I'm pretty sure it can.  The file is on another computer and I am using a mapped drive letter.  The UNC path may be the problem.  I'll report back on this.  Thank you very much for taking the time to reply. Smile

    Thursday, December 20, 2007 3:30 PM
  • SQL Agent doesn't create a full user session, so it doesn't have access to mapped drives - I'd guess going to the UNC path should solve it, as long as the Agent account or proxy account has permissions to access the share.

     

    Thursday, December 20, 2007 3:35 PM
    Moderator
  • It was the mapped drive, thanks!  I didn't realize that the mapped drives wouldn't be available.  I should have been using the UNC path in the first place, but at least I know about Agent not creating a full session...and I won't soon forget. Wink

    Thursday, December 20, 2007 4:11 PM
  • You beauty!

    I have been fighting this all day. I played with proxies, package security settings, credentials to no avail.

    Turns out my ODBC connection was using a mapped drive.

    Thank you

     

    Thursday, November 24, 2011 12:10 AM
  • Hi Jwelch,

    I am still facing issues with the execution of the job. My package is very simple take data from a table and load it to flat file. I created a login, credentials, proxy (used the credentials) and the proxy user has access to the shared path, i gave UNC (\\servername\test\test.txt) but still the job is failing with error "Executed as user: proxy account. The package execution failed. The step failed". i am able to execute by using UNC path using ssis directly. I have given the package file location in the job step.

    Thanks,

    Eshwar.

     

     


    • Edited by Eswararao C Thursday, November 24, 2011 6:17 AM
    Thursday, November 24, 2011 6:16 AM
  • Additional information i have deployed the packages on to the server and executed them by connecting to the integration services it got executed successfully. Only issue comes when i put it in a job.

    Regards,

    Eshwar.

    Thursday, November 24, 2011 6:44 AM
  • any update on this issue??

    Thanks,

    Eshwar.

    Friday, November 25, 2011 3:54 AM
  • Thanks  Jwelsh Mariner . 

    I have used the mapped drive connection to get files. It worked fine in visual studio not through the server agent.

    But i changed to the UNC path it worked fine through the server agent.

    Wednesday, February 4, 2015 12:42 PM