August 10, 2004 at 4:06 am
Hi,
I manage a set of DTS packages that perform a simple transfer from one Source DB to different target DBs (about 150 different target dbs). Unforturtunately some bright spark has decided to change the login to the source database, which is used in every package. Is there any way of mass updating the uid/password for the packages without having to open & save each package in turn?
Alternatively, Is there a way just having 1 package instead of 150 and switching the target DB dynamically.
Thanks in advance
Adrian.
August 10, 2004 at 7:17 am
Hindsight is a great thing, but I will tell you anyway.
If you had used a readconfig file to specify the db and password etc, you could have just changed it one place for all packages using the file.
Sorry. I do not know how to change them all globally.
August 10, 2004 at 8:25 am
Solved (almost)
I've actaully created a generic task that replaces all of my individual packages.
I altered the step on the schedule so that it passed parameters (using /A). Then I used the combination of dynamic properties and global variables to get the correct DB name, uid and password onto the target connection.
The only problem I've encountered is that my ODBC connection was using the Merant driver which doesn't like you changing the properties at run time. Switching to the MS ODBC for Oracle driver fixes this with only a slight performance cost.
August 11, 2004 at 10:13 am
Sounds like you've got it covered. Good to hear. I will have to take a look at your solution. Never know when it might come in handy in a new job.
August 11, 2004 at 10:58 am
Could either of you post an example of i.e. one using the readconfig file and/or two the solution you came up with?
Thanks!!!
August 12, 2004 at 3:01 am
This article covers the reading the connection properties from a file
http://www.databasejournal.com/features/mssql/article.php/3073161
To pass variables to a called package (how I did it), simply.......
Define global variables in the parent package (don't put any values in).
Define the same ones in the child package also
In the call to the sub package, open the properties dialog and add all the global variables to the list of 'Outer Package Global Variables'
If the job is scheduled, edit the schedule and add the parameters to the call to DTSRun.exe i.e.
DTSRun /S Server /E /N MYDTSPACKAGE /A DB_UID:8=sa, DB_TIMEOUT:3=100
Note: If the params are encrypted, just delete the /Z and everything after it.
The variable names you use in the call must be defined as global variables in the package you are calling.
To use these passed in values, setup a dynamic properties task as the first thing in your DTS package. Overwrite the connection properties with the global variables you have set.
Hope this helps.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply