SQL Server Performance

Making a massive change in connection objects for all DTS packages

Discussion in 'SQL Server DTS-Related Questions' started by EMoscosoCam, Nov 29, 2007.

  1. EMoscosoCam Member

    I would like to make the following: For each DTS package in a server, for each connection object, when the server name points to "Server1" now I want it to point to "Server2". Suggestions to do so?
    Thanks a lot.
  2. anandchatterjee New Member

    Instead of cghanging each connection property why dont you refer to a udl file? If you once change the connection string in udl it would reflect all the DTS package.. I think it may help you out!
  3. EMoscosoCam Member

    Problem is that the DTS packages are already created and are more than 40. I would like to automate the change of all connection objects, because there is a project to migrate all databases from a SQL Server 2000 to a SQL Server 2005; recoding the DTS packages to SSIS packages would be a second phase.
  4. anandchatterjee New Member

    I think, you need to change them manually!! I dont have any other option!! Waiting for some other experts option..[:S]
  5. ranjitjain New Member

    Automating the connection details changes can be achieved by writing your own code in vb. Steps which you will be doing manually, need to code in vb application i.e. in vb write code to read and open dts package one at a time and then loop through the connection objects, once connection matching the criteria is trapped modify with new details and save back.
    But how long it could take to write and testing the same application I'm not sure.
    Otherwise you are already aware of manual steps, if it is just one time task.
  6. satya Moderator

    I don't see any other option to change the packages with new server name rather than going for new lookups, 40 I don't see any issues and this time you might declare a variable to take the servername automatically rather than hard coding.

Share This Page