Hi,

Is it possible to change the connections of a DSN dynamically via DTS?
I need to pump data to approx 100 different destination servers and am
hoping that it is possible to do this by simply changing the Server
name/IP of a DSN.

Any help is greatly appreciated.

I remember doing something similar using Dynamic Properties. From the Help
'You can change package properties at runtime with values from an .ini file,
query result set, global variable, environment variable, data file or
constant'.

Ben Nevarez



"enzat" <enzat (AT) spotlight (DOT) com.au> wrote

Hi Ben,

I've tried something similar myself, but haven't been successful.
Are you able to offer more detail?

Thanks

Enzat,

Try this. In your DTS package (lets call it MyDTS.dts):

1. Create a Dynamic Properties Task
2. Create two global variables. One is for Server (lets call it
gvServer) and one is for DB (call it gvDB)
3. Create a job and use "dtsrun" to execute the package. Use /A for your
global variables. Open T-SQL window and write this script:

declare @dts varchar(100)
set @dts = 'dtsrun /Fc:\MyDTS.dts /NMyDTS /AgvServer:8=' + @@servername
+ ' /AgvDB:8='+ @@DB_name

exec master.dbo.xp_cmdshell @dts

Hope this helps!


*** Sent via Developersdex http://www.developersdex.com ***