Universally available ini file
-
SQL Server 2000 SP3 (migrating to 2005 over the next 18 months)
Following a server failure and some wasted time going through DTS packages resetting server names, I want to create an INI file to store them all and use Dynamic Properties tasks in every package, so that next time I have to change one file and all my interfaces (which run as SQL Server jobs) will continue to run. My question is where to put the INI file, which needs to be a network location which will always exist, irrespective of hardware failures, upgrades etc. (I'm not looking for a hot swap solution in the event of a server failure, but would want to be able to recreate the network location pretty quickly if I had to). I don't want to have to go back through all the packages to change the location of the INI file, as this spoils the point of the exercise. I have tried using DNS names instead of the server name, but since we moved to a Windows 2003 domain with Active Directory, the names aren't being resolved.
One suggestion was that I put the INI file on our intranet server, but that doesn't seem to be an option when setting up the task. Another was to use a virtual server, but setting one of these up solely to host a 400 byte file does seem like overkill.
Or am I missing another, more elegant solution? I'd be grateful for any pointers.
--
Scott -
We use a database table to store this info. If the server is up and running then the table is available.
In fact we store all Global Variables for each package in this table, much the same way that the new SQL 2005 Integration Services can store configuration info in a database.
You'll need some combination ExecuteSQL, ActiveXScript and Dynamic Properties tasks to extract the values and set the appropriate properties.
--------------------
Colt 45 - the original point and click interface
-
If the name of the database server which holds this table changes, won't I then have to go through all the jobs and change the location from which they retrieve the server names (our jobs run on a number of different servers)? I'm really hoping for something which will allow me to make a change in one location and then not have any more work to do.
--
Scott -
We run DTS packages on multiple servers and they each read their config info from a local table.
For administration, each of these tables are edited via a central ASP.Net webapp. That allows me set the values for any of the global variables for a given package.
--------------------
Colt 45 - the original point and click interface -
Also, we have a MSX/TSX setup so for some of our older DTS packages that don't read their config dynamically, we alter the job on the MSX server and then changes are automatically pushed out to the TSX server.
--------------------
Colt 45 - the original point and click interface
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply