Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: How do I export a DTS from one server and import into another?

    Good day all!

    Hopefully this is just a quickie but we'll see, won't we?

    I have a DTS package that needs moving from one server to our test bed, which is completely disconnected from the network - therefore I have to transport it by removable media (USB flash drive for instance).

    Is there anyway I can do this? I've tried doing a "save as..." Structured Storage File (*.dts) but then I have no clue as to what to do with it to get it back in to the database!

    Any suggestions me hearties?
    Yarr!
    George
    Home | Blog

  2. #2
    Join Date
    May 2007
    Posts
    23
    Yeah It is a quite easy if you are using same version of SQL on two boxes...
    From Enterprise manager, browse the DTS package and right click -> All Tasks -> Export and from options select Text File and point the file path.
    Copy this file on the disconnected box and Import.

    Hope this will help you.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Dead easy this one. Translate it into T-SQL, delete the DTS package and then it is a matter of copy & paste

    I've never had much luck moving DTS packages around (portability is another nail in their coffin IMHO). Have you tried googling? You will find lots of articles about this. Also check out http://www.sqldts.com/ - there ain't much to do with DTS that ain't on there.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by poison
    From Enterprise manager, browse the DTS package and right click -> All Tasks -> Export and from options select Text File and point the file path.
    Copy this file on the disconnected box and Import.
    It appears to be trying to export the tables as well? I think I might just be missing something
    Quote Originally Posted by Poots
    Translate it into T-SQL, delete the DTS package and then it is a matter of copy & paste
    Yes, I'd like to have avoided using a DTS in the first place, but truth be told - I have no idea how to import (and export!) CSV files into tables in T-SQL

    Yes I've tried googling and tripped over the same sight you posted a link to... Unable to find what (think) I was looking for.

    Oh and another justification for DTS in this case - it needed to be done quick. It's for the purpose of the first bulk load of data from another source a number of times (test, test and test again!).

    Any other ideas peoples?
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Yes I've tried googling and tripped over the same sight you posted a link to... Unable to find what (think) I was looking for.
    Really? Article linked from the home page:
    http://www.sqldts.com/204.aspx
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Yes, I'd like to have avoided using a DTS in the first place, but truth be told - I have no idea how to import (and export!) CSV files into tables in T-SQL
    Bulk insert - wrapper for BCP. Check out in BoL. Gets the stuff in:

    Code:
    
    BULK INSERT my_table FROM 'C:\stuff.csv'
    WITH ( codepage = 'RAW', DATAFILETYPE = 'char', FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 1, BATCHSIZE=1000, MAXERRORS=100000)
    
    BCP for knocking it out:
    Code:
    
    EXEC master..xp_cmdshell 'bcp "SELECT * FROM my_table" queryout "C:\stuff.csv" -c -T -t","'
    
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    in sql 2K all you have to do is open up the package in the designer and do a save as structured storage file. Then on your other server all you have to do is open that file and save it to that sql server.

    you will have to change the properties of your connection objects and anything else that points to things on the network. it's easy.
    “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.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The simplest method is to use Enterprise Manager to open an existing package, and then select Save As from the Package menu. You can then save the package directly to another server, or change the storage location to save it to a structured storage file if you do not have direct access to the final destination. The storage file can then be manually copied to the destination server. At the destination, open Enterprise Manager and right-click the Data Transformation Services node to access the Open Package option. This allows you to select a structured storage file package to open. You can then use the Save As option to save it to the new server.
    I don't quite know how I missed that bit!

    Poots, what does; "-c -T -t" mean/do?
    I'm going to look into making it into a SP after we have the thing running anyhow. For now we needed the quick solution so that's what I provided.

    Thanks guys!
    I'll be back to bug you when I'm scripting this out soon
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Poots, what does; "-c -T -t" mean/do?
    Would you like me to type BCP into the BoL search for you?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm getting lost in acronyms... BCP = Best Common Practice?
    Nope, apparently it's Bulk Copy Program...

    If I get time later I'll go look it up properly (gotta hit the test bed now if I still want to go on holiday next week )
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BCP is a very fast method of moving data about. Remember the "remove identity property from column" thread? One suggested solution was BCP the data out, change the table, BCP back in.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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