Changing Global Variables
-
Hi,
I have 100+ DTS packages that is copying from production to the dev server.
Each package has a 3 Global Variables (GVs): 1.Source_fille_path; 2. error_fille_path; 3. DB_Connection_String. All of the them have are same value in each package.
On the development server all 3 GVs need to be change to new values -of the development box.
Is it the only way to manualy open each DTS and change GV values?
Thank you,
Leonid
-
You can do it programmatically with a wee bit of ActiveScripting.
You would need to get a list of packages from the server (via ADO or DMO), load the package using the LoadFromSQLServer method. Once loaded you can then modify the values of your GVs and then save the package using SaveToSQLServer.
Bear in mind that you will lose any text annotations and the package layout.
--------------------
Colt 45 - the original point and click interface
-
What you really want to do is go back in time with a wet kipper and slap the person who didn't put these settings into a central location. Given that one of them is a db connection string, I would suggest an ini file. Everyone seems to have gone mad for XML these days but the ini file can do a great job here (might want to check that 2005 still supports it).
Our practice is to set up a directory on each server in exactly the same place - this could be as simple as C:\DTSIniFiles. We don't use a UNC path as this would be different in the different environments. Then every package has a dynamic properties task to set the GVs up.
The only man-trap here is that you need to keep each section of the ini file short - I've seen various descirptions of the limit, but as a rule of thumb I use a maximum of 10 entries per section.
You might still use this approach because you could copy and paste the dynamic properties task into each package.
HTH
Bill.
-
You could store all the dev and production settings in a table on a dd in sql server and then execute a Store procedure from the DTS package to get the values you need for dev or prod and assign the GVs output parameters
Or create a stand alone DTS package which performs the above then you call this new dts package and pass back the results.
-
Actually, this sort of approach is recommended as "Best Practice" by Microsoft in developing the routines for the Business Intelligence Accelerator.
What I do here is have all my global variables, connection details included, stored in a database. I pass in the server and database names for where this tables is located as global variables.
The first step in my package uses and ActiveScript task to assign the passed global variables to the appropriate properties for a connection to the above server and database. Then an ExecuteSQL task extracts the list of global variables from the table into a global variable recordset. The third step is an ActiveScript task that dynamically creates all the global variables. The last step in the setup sequence is a Dynamic Properties task that assign any other connection properties or other dynamic step/task properties.
The table that stores the global variables can be stored on any server, not necessarily the same production server. I use the package guid as the unique key, so this means I can store global variables for packages on all my production servers in the same table.
When packages migrate from development to production, the entries in the dev/test table are migrated across to the production table. The only other thing that changes is the parameters passed to the package at the start.
At the moment I'm working on an ASP.NET C# app to facilitate adding and updating the global variables.
--------------------
Colt 45 - the original point and click interface -
Everybody,
Thank you very much for your great suggestions.
Leonid
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply