Post reply

DTS failed when schedualed to a job

  • October 7, 2004 at 10:15 pm

    #60536

    platform: Win 2k Server, MSSQL server2k std edition:

    Log on to SQL server as local admin, create a DTS package which includes a few steps. the third step is Transfer SQL Server Objects. This step copies data from a few user tables to another db on the same server( only copies data into existing empty tables). The step uses sa account to connect to source and destination.

    After saving the package, execute package is successful. However, when schedualing it to a job, then running the job. The Package fails at the third step. Error:" Access denied". The job is owned by the local admin account who also owns the package. The failed step (Transfer SQL Server Objects) uses "sa". It's thrown me out.

    Can anyone help? Thanks.

  • October 11, 2004 at 8:00 am

    This was removed by the editor as SPAM

  • October 11, 2004 at 9:04 am

    #526193

    Hi,

    To schedule a DTS package is a little challenge. Here is a link to help you...

    http://support.microsoft.com/?kbid=269074

    Regards

    JFB

  • October 11, 2004 at 10:07 pm

    #526279

    Thank you site owner and Enthusiast. I will look into the anwser posted, and reschedual the package. Will post what happens later.

    Thanks

    YQ

  • October 12, 2004 at 5:57 pm

    #526439

    I had a read of the article posted by Enthusiast. The security context in my case is not an issue. However the article reminds me that when I ran the package from my machine, which worked was actually ran on my machine, not the server. When schedualed to a job, it ran on server which failed at the step Transfer SQL Objects. This step has a cript file directory which was based on my machine. So I changed the directory path according to the SQL installation on the server, and it's worked.

    Thanks Enthusiast.

    YQ

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply