Results 1 to 8 of 8

Thread: DTS steps

  1. #1
    Join Date
    Jan 2007
    Posts
    13

    Unanswered: DTS steps

    I am going to be moving multiple databases to a new server. Everything should go smooth, but I need to change a lot of the DTS packages that reference the old servername and replace it with the databases DNS record.

    Is there an easy way to get a list of which dts reference the old server explicitly (not using database DNS)?

    Thanks.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You could save the packages as VBS files, and do a search through those. Deciphering which instances of your server name are meaningful, and which are not, and of course, where in the package they are being used is quite a different (and usually difficult) matter.

  3. #3
    Join Date
    Jan 2007
    Location
    Sacramento, CA
    Posts
    6

    Going Forward

    This is why our company has chosen not allow DTS packages to be saved with the MS SQL server. All of our DTS packages are saved as Structured Storage Files (*.dts) that run with programs such as RunDTS.exe. In this design you pass global variables such as connection objects name like server names and file names. This allows you to port application and database on any named server easily. This also allows us to adhere to change control because the DTS objects can be checked into Visual Source safe forcing code change control. At any time the code librarian can lay down the last approved install. Also if you store it on MS SQL your DBA who normally has global access can update it. If you run them as scheduled task using only functional ID that are specific to each application with only minimal writes to do then job it also forces security within each application.

    Just an idea.

    Oh ya. If you choose to script them all out as vb scripts (*.bas) you can use a product called Ultra Edit or Visual Studio (Find in File) to scan all those bas scripts and look for the server names.

    Good luck

    Jim

  4. #4
    Join Date
    Jan 2007
    Location
    Sacramento, CA
    Posts
    6

    Going Forward

    Sorry - dupe

  5. #5
    Join Date
    Jan 2007
    Posts
    13
    Thanks for the info! I'll give it a shot next week and let you know what happens.

  6. #6
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Also if you store it on MS SQL your DBA who normally has global access can update it
    From the DBA point of view it's also easier 'cause he will not be held responsible when a DTS-job fails (RunDTS.exe is scheduled from the application servers, not as a SQL Server job)

  7. #7
    Join Date
    Jan 2007
    Posts
    13
    Turns out that this will not work for us. We need to move an entire instance to another physical server.

    This link provides a tool that easily moves DTS packages from one server to another.

    http://www.sqldts.com/204.aspx

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I tried that once. Had trouble. I have a script somewhere that moves DTS jobs between server. If I find it, I will post it. It was pretty simple though. It might have been as simple as copying between msdb.dbo.sysdtspackages.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •