Post reply

Changing Job step properties

  • April 2, 2012 at 1:57 pm

    #258698

    Hi,

    I've been looking for help changing SQLAgent job step properties. Specifically I keep all my SSIS ETL packages in MSDB. I had to create a new server and import the MSDB from another production box. That works great but the only issue is the job steps that use SSIS packages the package source is set to the old server name. I need to change all these to the new server name. I can do this by opening each job and editing the step manually but that's a little painful. Can anyone point me to a system view or proc that I can use to change the server name?

    Thanks!

  • April 2, 2012 at 10:37 pm

    #1468005

    scribesjeff (4/2/2012)

    Can anyone point me to a system view or proc that I can use to change the server name?

    sysjobsteps

  • April 5, 2012 at 9:27 am

    #1469629

    Hi,

    Thanks for pointing me to the view. Here's the solution if anyone is looking for it.

    UPDATE sysjobsteps

    SET command = '/SQL "PATH TO PACKAGE" /SERVER NEW_SERVERNAME /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'

    WHERE command = '/SQL "PATH TO PACKAGE" /SERVER OLD_SERVERNAME /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'

  • April 10, 2012 at 10:05 am

    #1471315

    Thanks for posting the solution.

    I think updating system tables should be avoided.

    Can sp_update_jobstep be used for this?

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

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