SQL Server Performance

DTS transfer job gives error

Discussion in 'SQL Server DTS-Related Questions' started by kjp948, Jul 5, 2006.

  1. kjp948 New Member

    I am able to schedule a job to import data from text file to sql table ,

    when i schedule job all other types of jobs work but
    DTS job gives error every time.

  2. dineshasanka Moderator

    First of all, what is your error can plese that error on this forum.
    is this DTS working when you run manually.
    check the user persmission of SQL Server agent user

  3. kjp948 New Member

    Thanks Dinesh,

    I tried to find error in error log but there is nothing about the transfer job i did.

    but i found on job history this message "The job failed. The Job was invoked by Schedule 41 (trialtransfer). The last step to run was step 1 (trialtransfer)."

    I am new at DTS transformation , so i dont know whether i need particular permission to run DTS job or not.

    I have these permissions,

    public, db_owner, db_accessadmin, db_securityadmin,db_ddladmin, db_backupoperator, db_datareader,

    Is there any other permission i need to use SQL agent?

  4. dineshasanka Moderator

    it seems that Schedule 41 is the problematic place. Can you check the schedule closely

  5. kjp948 New Member

    It is very simple transfer package from table to text file.

    I really dont know what else to check.

    The main server is located on another computer which i already check whether SQL Agent is enabled or not .

    Every thing seems fine there.

    let me know if anything else to check

  6. ranjitjain New Member

    Can you run manually the DTS and check whether it errors or not
  7. kjp948 New Member

    Yes I can run it manually,

    Somehow when I am transfering from table to text file
    SQLAgent can not access that text file in scheduling.

    Do I have to save it to some drive where actual server is or it will be ok anywhere.

Share This Page