[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

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

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
PKTG
Asked:
PKTG
2 Solutions
 
Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
0
 
PKTGAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can script the jobs and run the script in the new SQL Server instance.
ScriptJobs.JPG
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
DcpKingCommented:
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
 
ZberteocCommented:
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
 
PKTGAuthor Commented:
I've requested that this question be deleted for the following reason:

Moved manually
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now