Solved

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

Posted on 2014-10-08
7
59 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
7 Comments
 
LVL 24

Assisted Solution

by:Mohammed Khawaja
Mohammed Khawaja earned 250 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 45

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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 26

Accepted Solution

by:
Zberteoc earned 250 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now