Loading DTS Packages
-
Hi folks.....
Got a question about loading DTS Packages onto a server.
Situation: we have a set of DTS packages that have to be installed on customer sites as part of our software product. The installation has to keep the on-screen layout intact AND change the server names in all the connection(s) set up in the package from our development server to the customer's server.
I've been able to use the object model to load a package from a structured storage file and change the server name - but this doesn't preserve the on-screen layout.
I've been able to use a bulk insert with an intermediate table to load a package from a file created with bcp into the msdb.dbo.sysdtspackages table - this keeps the on-screen layout and lets me remove older versions of the package, update owner details etc., but doesn't allow me to change the server name.
Does anybody know of a way to load the package that will allow me to change the server name automatically AND keep the on-screen layout intact? Preferably without using a 3rd-party application that'll complicate our installation processes etc. & which I'll have to fight our finance guys to buy?
Any help would be much appreciated.
Thanx
Dave.
-
Dave, when you say "keep the on-screen layout intact", do you mean how the DTS package looks in DTS designer? If so, continue to use the BCP method you mention.
You can add a dynamic properties task at the very beginning of the package to adjust any server settings within the package. This can set any property, using the query SELECT @@servername, to the current server and will be executed every time the package is run.
The bonus of this is that your client can move the DTS to another server and it should still work.
Does this help? Or have I missed the point completely?
-
Hi Adrian
Yes, you got my meaning correctly - no problems there.
I was afraid that would be the case with server links..... unfortunately, some of the packages in question are using multiple servers, none of which are linked, so the dynamic properties task you suggest (which will, I assume, be restricted to looking at the local server) isn't going to work.
We found a way round the problem by using Data Links, referencing local UDL files, instead of hardcoded server connections..... and giving the customers a set of UDLs for each of their servers. This seems to be working OK, so we're going to stick with this setup in future.
Thanx for your help.
Regards
Dave.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply