Post reply

DTS job scheduling problem

  • October 20, 2004 at 2:17 pm

    #59538

    Hello,

    I have a package with a DDQ connecting a FoxPro table to SQL server. Its not all that compilcated, It has a lookup, a Pre source and a Row transform transformation.

    I can run this just fine from the designer or straight out of EM, but when I schedule the job it fails - and the event logs simply says that the DDQ failed.

    Any suggestions ?

    TIA,

    Ken

     

     

  • October 20, 2004 at 2:26 pm

    #527366

    I would do a quick check on connections for user (and in this case server) privledges.  Easy to forget that through EM everything is executed under your account and not the SQL Server.  Just one idea.

  • October 20, 2004 at 2:50 pm

    #527370

    I also had an instance where the database was one version 7.0 and the client tools were another 2000 that did the exact same thing.

  • October 21, 2004 at 9:08 am

    #527482

    As Osoba stated above, check that the SQL Agent has the same privileges that your user account has, otherwise the job will fail, because a job is run using the credentials of the SQL Agent account.  Also make sure that this account has access to the same LAN directories if the datasource is being pulled over the network.

  • October 21, 2004 at 7:48 pm

    #527564

    As stated you have issues with the credentials. When you run a DTS package using the job sceduler within SQL it uses the account that SQL uses for the service... usualy the local system account. This account doesn't have any rights on other machines... You could set SQL to use a domain account that has rights (over kill...) or you can use windows scheduler to run the dts package with DTSRun.... DOS command line tool using an account that has rights on the local machine to run the package and rights on wherever the data comes from... I have my own means for doing this that uses the DTSRun /~....... from the job withing SQL scheduler but you don't have to use that.

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_dtsrun_95kp.asp

    Enjoy..

    Michael

  • October 22, 2004 at 2:13 pm

    #527713

    I rebuilt the package on the SQL server per se and got it. I also had to install an OLE DB VFP driver on the server that was on my workstation only.

    Thanks for the replies, they helped.

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

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