Good day all!
Hopefully this is just a quickie but we'll see, won't we? :p
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!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.|||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.|||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
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?|||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|||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-SQLBulk insert - wrapper for BCP. Check out in BoL. Gets the stuff in:
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:
EXEC master..xp_cmdshell 'bcp "SELECT * FROM my_table" queryout "C:\stuff.csv" -c -T -t","'|||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.|||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 ;)|||Poots, what does; "-c -T -t" mean/do?Would you like me to type BCP into the BoL search for you? ;)|||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 ;))|||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.
