Solved

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

Posted on 2014-10-08
7
96 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 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 50

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure 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 27

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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

690 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