Post reply

Scheduled DTS Package stopped running

  • March 8, 2007 at 2:13 am

    #194482

    Hi All

    I had a strange issue this morning which I would like to investigate to prevent it from occuring again.

    I have a DTS Package that runs a stored procedure. This package is scheduled to run once a minute and takes no more than 5 seconds to run. I noticed in the history that at 8:10 this morning it was failing each time it ran. There had been no changes to any procedure or package and if I run the dts package manually it works fine. I resolved the problem by simply deleting the schedule and recreating it.

    Any idea what caused the problem or where I can look to find out?

     

    Thanks in advance.

    Regards

    Steven

  • March 8, 2007 at 3:48 am

    #693758

    stock answer - check the logs. Did you have dts package logging turned on ?

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • March 8, 2007 at 9:43 am

    #693857

    Colin, thanks for your help. DTS logging was turned off. I have now enabled it and will keep an eye on events.

     

    Many thanks

    Steven

  • March 9, 2007 at 5:44 am

    #694052

    I just had one passing thought, from experience, if you've scheduled the dts to run through a job which runs the job by the guid, then a casual save of the package, even without a change, will generate a new guid and the scheuled job will fail. IF your packages run this way I suggest changing to   DTSRun /N "Package Name" /E /S "server or instance name" , this resolves many problems!!

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • March 9, 2007 at 6:04 am

    #694060

    Thanks for that. I have only ever created schedules using Enterprise Manager by right clicking and selecting schedule. I never realised you had the option of guid or name. The package was indeed scheduled my guid but has now been changed.

    Thanks again.

    Steven

  • March 12, 2007 at 7:10 am

    #694346

    Stephen, If you schedule a job from the local packages folder by right clicking, it will give the guid to the step, but if you create a new job from the SQL Agent - Jobs folder and create the job you can put the name of the package after the DTSRUN command.

    _______________________________________________________________________
    Work smarter not harder.

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

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