Kristian_Moritsen
asked on
Edit *.DTS packages before importing
Hi!
I have a little challenge:
I have 4 different SQL servers which need to upload/transfer data to a 5 SQL server.
All tables etc. is exactly the same on all 4 servers.
I have made a DTS packages on one of the servers which works fine so now I just have to distribute this packages to the rest of the servers..... The problem is that I have no access to these servers and there is no one there with knowledge about SQL/DTS (not that I have) but I'm sure that I can send them a *.dts file and explain them how to import it BUT then it will have to work without further modifications!! Are there anyway that I can modify a *.dts file in a text editor or something like that before sending it?
Kristian
I have a little challenge:
I have 4 different SQL servers which need to upload/transfer data to a 5 SQL server.
All tables etc. is exactly the same on all 4 servers.
I have made a DTS packages on one of the servers which works fine so now I just have to distribute this packages to the rest of the servers..... The problem is that I have no access to these servers and there is no one there with knowledge about SQL/DTS (not that I have) but I'm sure that I can send them a *.dts file and explain them how to import it BUT then it will have to work without further modifications!! Are there anyway that I can modify a *.dts file in a text editor or something like that before sending it?
Kristian
ASKER
Hi!
Solution 1. seems like something I could use - I give it a try!
I have I tried to export (Save As) the packet as a Visual Basic file and then I can use find and replace and all the things I need but is there a way to Import/load a bas file into a DTS packet or a way to convert a BAS file to a DTS file????
Kristian
Solution 1. seems like something I could use - I give it a try!
I have I tried to export (Save As) the packet as a Visual Basic file and then I can use find and replace and all the things I need but is there a way to Import/load a bas file into a DTS packet or a way to convert a BAS file to a DTS file????
Kristian
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, Local would do it if no other servers were involved and the same username and password could be used on each target server.
AustinSeven
AustinSeven
1. Redesign DTS Package to read a config file
One solution, in concept, would be to modify your DTS packages to read in a config file (ActiveX script might be best for this). Then you could distribute a generic DTS package. This would be exported or 'Saved As' from your development server and then simply opened up and re-scheduled on the target servers. The config file could be distributed with it - ready with the correct variable info contained within it - eg. server names.
2. Reduce the amount of changes required using a kind of 'connection library'
In general, the items that will need chaning in a DTS package, when migrating it to a different box, are the connection objects that specify the server names, usernames and passwords. One half-way solution if you haven't got time to implement the config file thing is to setup a simple version of a 'connection library' in the DTS package. For example, in design mode, create a standard looking section in the top left of the package and enter the text, 'Connection Library'. Below the text, create all the connection objects that you will need. Don't link them to anything. This way, you can change 'Server1' once and all the other Server1 connection (objects probably lost somewhere deep in the maze) will be changed to. Although this would still involve you writing some instructions for someone else to edit the package, it would be relatively easy to do - easier than asking them to locate the items in the maze.
3. Use DTSBackup
For a 3rd party solution....
http://www.sqldts.com/default.aspx?242