SQL Server Performance

DTS Package Security Problem

Discussion in 'SQL Server DTS-Related Questions' started by jburdios, Mar 31, 2004.

  1. jburdios New Member

    Hello! I am new to using DTS (SQL Server 7.0). I created a package that runs successfully when ran locally but fails when scheduled as a job. The package queries 2 tables in SQL Server, uses transform data task to create a destination file locally, then copies the file to a file server. THANKS FOR YOUR HELP! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Here are the details:<br />DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217887 (80040E21) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
  2. gaurav_bindlish New Member

    Check the account under which SQL Server Agent is running. The account should have permission to access the File Server share.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  3. jburdios New Member

    That worked!!! I added the sql agent account and added it the to Admin group. THANKS A BUNCH! [:I]

    Here's what's weird: Just for kicks, I deleted the sql agent account from the file server to see if the job would fail again. However, the job runs just fine. The job owner had admin rights to the file server before and after granting rights to the sql agent account. Can you explain this?


    quote:Originally posted by gaurav_bindlish

    Check the account under which SQL Server Agent is running. The account should have permission to access the File Server share.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  4. derrickleggett New Member

    The permissions are cached.<br /><br />Just for fun, reboot the file server and see if it still works. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Derrick Leggett<br /><br />

Share This Page