Post reply

Transfer select jobs from SQL Server 2012 to 2016

  • July 10, 2019 at 6:56 pm

    #3661710

    We are migrating from 2012 to 2016 and we have over 300 jobs currently.  I am not going to be moving all jobs and want to be able to use t-sql to identify the jobs I will transfer based on properties of the job.  I would like to use the Transfer Job Task in SSIS to make this repeatable (testing then live).  I was hoping that I could set an expression on the JobsList property but it does not appear in the list.  I have done a bunch of Google searches and found some post with the same question but none have appropriate answers.

    If it is not possible to dynamically set the JobsList property, does anyone have another suggestion on how to accomplish this task?  I have search for t-sql solutions to this but haven't found anything yet.  Is there a "Transfer Job" function in t-sql?  Seems odd if there isn't.

  • July 10, 2019 at 7:09 pm

    #3661713

    I do not have a solution, though maybe there is a a clumsy workaround.

    Use the Transfer All Jobs option, then adapt your existing T-SQL to drop those jobs on the target server which are not required.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • July 10, 2019 at 7:38 pm

    #3661722

    Thanks Phil.  Looks like that is going to be the best option for this project.  Disappointing that MS doesn't allow setting that property for the task.  I appreciate your suggestion.

    Scott

  • July 10, 2019 at 7:45 pm

    #3661725

    have a look at Dbatools - probably has what you need https://dbatools.io/commands/

  • July 16, 2019 at 7:33 pm

    #3663559

    Check out dbatools.io

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

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