Post reply

Migrate DTS Package to another server

  • September 18, 2006 at 9:17 am

    #192368

    Is it possible to migrate / copy a DTS package from server A to server B ?

    When I save the DTS package on server A and open the package on server B all the connection are pointing to server A instead of server B ?

  • September 18, 2006 at 1:02 pm

    #661118

    You have to change the connections after moving the package unless you've used UDLs or a .ini file.

    See this thread: http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=308151#bm308999

    Greg

  • September 19, 2006 at 12:58 pm

    #661335

    You can reference the SQL Server connection as being (local), and it will always refer to the machine on which it is being run. Doing it this way, however, means that the DTS package always has to be run locally. If you try to run it remotely via Enterprise Manager or DTSRun, it will try to run on the remote machine even if it doesn't have SQL Server installed.

    What I generally do is to make a global veriable to define the SQL Server name and then my first step is a Dynami Properties Task that sets the Server Name on all SQL Server connections. Then, I only have to change the global variable declaration. Global variables can also have new values passed in to them at run time via the DTSRun utility.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • September 20, 2006 at 12:47 am

    #661421

    Hi Antoine,

    Yes, your DTS has to reference the right servers - those won't change by simply copying the package.  What I've generally done is save a copy of it as a structured storage file (.dts), and then open the copy and change the relevant attributes of the package.  The advantage of saving it like this is that I can then import the structured storage file directly onto a server runnning SQL2005, which has been a requirement in my setup.

    While my technique is fine for a reasonably simple file, if you have a lot of connections and processes to change in each package, it might be worth trying to set it up with global variables or a .ini file as Greg and Robert have suggested.

    -----------------

    C8H10N4O2

  • September 20, 2006 at 12:57 am

    #661423

    Thanx for all the replies, I managed to transfer my package to another server with the help of your replies !!

Viewing 5 posts - 1 through 4 (of 4 total)

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