DTS works but schedule JOB don't

  • I create a DTS to copy data and works fine when I run directly DTS, when I schedule a job on a daily basis, the job fail all the time..

    error is ==> The Job was invoked by user sa,

    the last step to run was step1.

    and there is only one step there.

    somebody knows what is that?

    thanks

  • 1) Is the place where tha package is built the same as the server it is saved to?

    2) If not what version is the client and the server?

    3) Are there differences that are on the client that do not exist on the server related to this package? (Mapped drive, etc.)

    4) Have you tried creating an error handling file to output the errors when run for more detail? (Open package and select Package/Properties on the tool bar, found under general tab in SQL 7, and logging tab in SQL 2000)

    5) Was the package scheduled from a 2000 server client on a 7 box?

    6) Can you provide more details about the error?

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • It's a SQL 2000 and the ODBC reside on the server and If I run manually, works fine

    but the job dont work at all.

  • If it is a scheduling problem, it could be a permissions issue with your SQLAgent account, as it is this that executes your jobs. Your SQLAgent account should be in your local admins NT group

  • Unless agent is running under system account which if it is the ODBC DSN then make sure the DSN is a system DSN and not USER or File.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • The Agent is running under my name/password and i have administrator rights on that server

  • When the job was create it should have created a DTSRUN encrypted version that it executes, open the job step and copy it out, then run command line prompt to see what happens? If it fails create one yourself and see if works? Just trying to get a grasp on where the failure actually is occurring.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • Did you create the package while sitting at your workstation or at the server itself?

  • Make sure when you create the package when you select the source you select it from the "Network Neighbouhood" not from a drive you have mapped on your PC, it will not recognize it.

  • Actually when you select the "destination" not source, sorry.

  • Microsoft has posted this article:

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;q269074

    I think it brings some light to this issue.

  • Here's an idea try recreating the project, and make sure that Owner is one who has permissions to run the package, preferable the SERVER\Administrator. Otherwise I like the IDEA of:

    DTSRun /~Z0x62729286A921383A451F7E72B1ADE2D008AE95B7CB6C74A87E94540E1F62E8C0DB2BB79B287FB2FE1A51835096208D7AB70358CFBB8F681F46D46CB29020FC0B9382B93E51714C908FD7290139ECF01677D1C9C35FEFFFDC0E51ED8D6E00F0AE7B0638CD294AE2229523B201969F2742B05064

    Grabbed from Steps--->Edit

    Grab that and run it in query analyzer like so

    EXEC master..xp_cmdshell 'DTSRun /~Z0xC4DCE73022D9D1818E5D424430B2C02CA25C602185BE4EF64609DE119B7F9A54D2733225B89C37DB3F708DE5A3EFDEB0B928E1B5F971039DD2BED27F09D0F399CC4F0EA19450EF0798D1C838DD4D1FE8868A7D9A2D13EFC1B5D110 '

    GO

  • Sorry took me so long to answer I take vacations, so I'm back.

    Yes when I copy and past and run from the query analyzer works fine, but the job still fails.

  • YOu need to install service pack 2. I had the same problem and once I installed service pack 2 everything was fine.

  • I also had the problem. You need to register your entreprise manager with another login (not SA better a login that is used only to run the packages).

    When this is done, edit your package and save as NEWNAME and then shedule the new one.

Viewing 15 posts - 1 through 15 (of 40 total)

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