Results 1 to 10 of 10

Thread: Transferring DTS Packages...

  1. #1
    Join Date
    Jul 2004
    Location
    SLC, UT
    Posts
    15

    Transferring DTS Packages...

    Hi Everyone,
    Have anyway come across any easy method of transfers DTS packages across two servers which are existing on two different networks.

    I haven't been able to use the method mentioned in article "DTS How to...Transfer Packages between Servers.." since the two servers I'm trying to move TO and move FROM are on two different networks which aren't visible to each other for my to register from the same box.

    Appreciate if someone to point me to a means of doing this or any articles that explain how to do this..

    thank you

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    You can save package as structured file, copy file to target server then load into dts.

  3. #3
    Join Date
    Jul 2004
    Location
    SLC, UT
    Posts
    15
    yeah ... but I have like 50-60 DTS packages... and manually saving each and coping files across and then opening them at destination and resetting all the paths seems a tedious process and prone to errors.. wanted some script and deploy process for deployment..

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    All you have to do is copy the content of msdb..sysdtspackages to another server.

    Check this article which describes it in detail.

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

  5. #5
    Join Date
    Sep 2002
    Posts
    5,938
    Anyway, you have to modefy server connection and so one by one.

  6. #6
    Join Date
    Jul 2004
    Location
    SLC, UT
    Posts
    15
    Thank you .. that article really helped.. but wonder how i'll implement that if I can't see the destination server from my source server.. what I mean is I can't add the destination server on my source SQL server box.
    Would exporting the data to a ANSI file and re-importing at the destination be a good thing to do ..(btw I tried that by having issues re-importing them at the destination.. will keep you posted if that works.. )

  7. #7
    Join Date
    Sep 2002
    Posts
    5,938
    You can backup msdb and restore it on target server with new name, then should be able to transfer data in syspackages.

  8. #8
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    All you need is records from sysdtspackages, so restoring entire msdb is not necessary. You can bcp out and bcp in.

  9. #9
    Join Date
    Jul 2004
    Location
    SLC, UT
    Posts
    15

    tried ..


    I haven't used must of bcp .. but I tried this but got a error saying UNable to connect to Host Name .. have you ever had that issue..

    Looked up error details on
    http://msdn.microsoft.com/library/de..._odbc_5stz.asp

    HY000 : Unable to open BCP host data-file.
    The file name specified in the bcp_init call does not exist or is opened by another application.

    what am I supposed to do here..

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    What was you complete command used? By the way, backup/restore is much easier and faster since msdb is so small.

Posting Permissions

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