DTS works but schedule JOB don't

  • NelsonVere

    Ten Centuries

    Points: 1220

    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

  • Antares686

    SSC Guru

    Points: 125444

    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)

  • NelsonVere

    Ten Centuries

    Points: 1220

    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.

  • Dave Francis

    Old Hand

    Points: 308

    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

  • Antares686

    SSC Guru

    Points: 125444

    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)

  • NelsonVere

    Ten Centuries

    Points: 1220

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

  • Antares686

    SSC Guru

    Points: 125444

    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)

  • maraclec

    Ten Centuries

    Points: 1175

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

  • vascris

    Valued Member

    Points: 65

    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.

  • vascris

    Valued Member

    Points: 65

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

  • ignacio

    SSC Veteran

    Points: 260

    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.

  • kbrady

    SSCommitted

    Points: 1861

    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

  • NelsonVere

    Ten Centuries

    Points: 1220

    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.

  • rocjohns

    SSC Rookie

    Points: 46

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

  • PAUL717

    Old Hand

    Points: 378

    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