Post reply

Moving DTS Jobs

  • January 5, 2005 at 3:14 pm

    #84504

    I have 21 DTS jobs that I need to move from one server to another. What is the best (and most accurate and painless) way to do this? Any advice would be helpful.

    Thank you in advance,

    Michael

  • January 5, 2005 at 6:46 pm

    #536099

    http://www.sqldts.com/

  • January 5, 2005 at 11:25 pm

    #536115

    I found this script to be particularly useful.

    -- /E : Use a trusted connection

    -- /!X : Do not execute the DTS package

    -- DTSRUN.EXE /S ServerName /E /N PackageName /F FileName /!X

    -- Copy the results and add to a batch file for executing

    DECLARE @TARGETDIR varchar(1000)

    SET  @TARGETDIR = 'F:\Uploads\'

    SELECT distinct 

     'DTSRUN.EXE /S '

     + CONVERT(varchar(200), SERVERPROPERTY('servername'))

     + ' /E '

     + ' /N '

     + '"' + name  + '"'

     + ' /F '

     + '"' + @TARGETDIR + name + '.dts"'

     + ' /!X'

    FROM msdb.dbo.sysdtspackages P

  • January 6, 2005 at 2:38 am

    #536128

    1. Backup the MSDB and restore on new server but you will over write any jobs already on the new server. So be sure there is nothing you need. If this is the case and you need info to restore MSDB let me know and I will give details.

    OR

    2. Open each DTS package and File/SaveAs Structured storage and copy files to new server. Open each file and save to server.

    To open File go to EM and Data Transformation Services and right click and Open. Open the file that you have saved for each DTS package and save it as Location: Sql server.

  • January 6, 2005 at 6:35 am

    #536159

    Thank you! Now, here is my next problem.

    I also have jobs I need moved from one server to another. Just like before, I need a painless and effective and efficient way to move these as quickly as possible. Anyone have any ideas?

  • January 6, 2005 at 7:07 am

    #536170

    I think the above posts already explained how to move them to a new server.  The method I have used is to open each package in the DTS designer in Enterprise Manager and select "Package" > "Save As".  The dialog box that opens up allows you to select a different server to save it to.  After you have done this for each of the packages you will have to open them up on the new server and change any database connections that may have changed due to the move to the new server.  It's a manual process but it gets the job done.

  • January 6, 2005 at 8:07 am

    #536196

    Thanks. I did that on a test box and if I wanted to change the jobs, it gave me an error saying I could not modify jobs that came from an MSX server, or something to that affect. Any ideas on why and how to get around it?

  • January 6, 2005 at 8:33 am

    #536211

    Are the 2 servers running the same version of SQL Server?  Same service pack version?  If you haven't already, try connecting to the package you created on the new server from your Enterprise manager on the old server (or wherever you were successfully opening the packages from on the old server) to see if it is a versioning issue with EM. In some cases you will have compatability issues when trying to open a DTS package in one version of Enterprise Manager that were created in another.  If it opens from there and you find out that the 2 machines are running different service packs then update both machines to the latest SP and try saving the packages over again.

  • January 6, 2005 at 8:40 am

    #536214

    I have written a VB.Net app that will compare/move/update DTS Packages and allow for migration of packages between different servers... If you are interested in trying this utility out, send me an email (mgercevich_NOSPAM@gmail.com  - remove "_NOSPAM") and i'd be happy to send you out a copy to try out.

    -Mike

  • January 6, 2005 at 9:35 am

    #536234

    Michael,

    I use DTS to move packages directly from msdb.sysdtspackages on one server to the same table on another server.  I took the instructions at http://www.sqldts.com/default.aspx?204  and added a Dynamic Properties task to set the source and destination computer names.

    To address your second question about jobs, I've found that scripting them out and running the scripts on the destination server works well.  To script a job, right-click on it in Enterprise Manager, select All Tasks, select Generate SQL Script.

    Greg

    Greg

  • January 6, 2005 at 10:49 am

    #536249

    Can you check value of the column originating_ server in sysjobs ? If it is different than the servername, you can update sysjobs and set it to the servername. This is not best practice but a work around.

  • January 11, 2005 at 7:03 am

    #536802

    Thank you all!! All of your tips have helped. I just have one more question (I hope).

     

    How do I move maintainence jobs from one server to another, or can I?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply