Can you use a variable to identify the server in a DTS script

[kprogrammer]

I am new to DTS and need to know if it is posible to use a global variable to identify the server when creating a DTS script. Our script will be tested on one server and thne run on a clients server using SQL server 2000.

 

[mrdenny]

Yes. You can use the Dynamic Properties Task to change the defination of the Connection Object.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[MDXer]

Is this for the Connection objects? if so you might want to look into using UDL files for your connection source.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[JayKusch]

In reading your post a bit more carefully it seems that you are going to create your DTS package on a "Test" server and then port it over to a "Client" server.

As long as you set you connection to the server as "LOCAL" and not the test servers name, it will port over to the client's server and execute without the need to intervene and change the connection to the clients server name.

As a side note, when you get ready to port your DTS package to the client server, save your DTS package as a structured storage file which creates a *.dts file in the designated folder in your drive.

You then copy that file over to the test server, open DTS designer and open the .dts file like you would open any other file.

Once it is in the designer of the test server, save it once more as a SQL Server package and BINGO ... it is on the test server ready to execute.

Thanks

J. Kusch

 

[kprogrammer]

Thank you all for the very helpful information! I will take it back to my fellow developers and we will discuss the best approach for our project.

Kirk