Wednesday, August 15, 2007

Transfer SQL Server Jobs from SQL Server 2000 to 2005

To transfer sql server jobs from sql server 2000 to sql server 2005 you can use SQL Server Business Intelligence Development Studio.

1. Click file -> new -> project to create a new Integration Services Project
2. Click view -> toolbox so you can see an overview of all the items
3. From the toolbox, drag the "transfer jobs task" to the control flow window
4. Right click the transfer job task and click edit
5. Select jobs and enter the source and destination server, you can test the connection immediately
6. Choose to transfer all jobs, or only the ones you can select from the given list
7. Set the desired IfObjectExists and EnableJobsAtDestination option and click OK
8. Right-click the transfer jobs task and click execute task

If the job fails it will turn red, if it succeeds it turns green. Click on execution results to check for errors.
A couple of reasons why the task would fail:

  • the job owner does not exist on the destination server, you can modiy this on the source server in the job properties

  • if you have e-mail notifications enabled on the job, be sure the operator exists on the destination server or disable this

  • if the database for which the job will be executed does not exist that job transfer will fail

A couple screenshots from the transfer jobs task properties and the execution results:


No comments:

Post a Comment