Solved

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

Posted on 2014-10-08
7
72 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 25

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 47

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

806 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