Transfer select jobs from SQL Server 2012 to 2016

  • 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.

  • 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
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • 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

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

  • 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