?
Solved

how to move SQL Server Agent Jobs (SSIS Packages) from one server to another SQL Server ?

Posted on 2014-10-08
7
Medium Priority
?
109 Views
Last Modified: 2016-02-11
We have SQL Server Agent jobs (SSIS jobs) in almost 30 servers. Now we plan to reduce 50% servers. So I like to move SQL Server Agent jobs (SSIS package, Job script & config files) from one server to another server without affecting existing jobs.  Can you please let me know the best and easiest method for this task? Thanks.
All the server has
1. Two  instances (A & B) of SQL Server 2008 R2.  
2. Same Database and tables for ETL purpose

All the jobs enter records in one of the prod SQL box database or Oracle database.
0
Comment
Question by:PKTG
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 25

Assisted Solution

by:Mohammed Khawaja
Mohammed Khawaja earned 750 total points
ID: 40369660
0
 

Author Comment

by:PKTG
ID: 40376213
Thanks. But i just need to move /deploy packages , jobs and config files from one server to another. I guess there is some simple and best option available in SSIS . Can someone please provide the details? Thanks.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40396812
You can script the jobs and run the script in the new SQL Server instance.
ScriptJobs.JPG
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 16

Expert Comment

by:DcpKing
ID: 40398476
Even if you script the jobs you may find that they depend on things that only exist on the server you're removing them from. The techniques described will all work - you may, however, find (depending on the time constraints involved) that pulling them out into BIDS and re-deploying them will be worth the effort, if you can discipline yourself to writing some docs on each as you do it, regarding what each one needs, etc.

hth

Mike
0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 750 total points
ID: 40399513
The easiest way to script all the jobs at once on a SQL server is from Object Explorer in Management Studio you expand the SQL Server Agent node > click to select the Jobs node > hit F7. In the list that opens you can select multiple jobs or all, depending what you need, and then right click on selection > Script Job as > CREATE To > choose whatever option you prefer.

You will have to go over the code and make sure that the scripts will be modified to accommodate the new host server. You then will take the script and executed on the new host server. This will take care of all the details, schedules included.
0
 

Author Comment

by:PKTG
ID: 41001747
I've requested that this question be deleted for the following reason:

Moved manually
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question