Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gar3th_jon3s
Starting Member
4 Posts |
 Posted - 2003-11-06 : 09:06:28
|
Hi, I've only been using SQL Server DTS for a little while and I would like to know if it is possible to refresh details of a package that has been changed programmatically without opening it and saving it? I have the problem that I have created many packages whose details need to change when moving from development to production environments - details such as connections, server names, logging etc. I have written maintenance programs to go through each package and automatically set these which is fine. However, the changes made do not reflect in the package when run. Only when the package is opened and saved again do the changes appear.Is this normal behaviour or have I missed something simple? If so, is there no way around this?!? I thought I was being cunning by writing these programs, but if I after open and re-save every package after every change it doesnt save me much time!! I have over 100 packages  any help would be great!CheersGareth Jones |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-11-06 : 12:36:31
|
| Well how would you do this with say a notepad file? In order for the changes to take effect for another process, you have to save the file. It sounds like you are going about things in the wrong way because this is the first that I have heard of something like this. Perhaps if you explain what is being done, we could help you out with a better solution.Tara |
 |
|
|
gar3th_jon3s
Starting Member
4 Posts |
Posted - 2003-11-07 : 05:03:20
|
| Cheers for the reply,Sorry if the explanation wasnt very clear, I will try and explain more clearly:I have a collection of around 100 packages that extract data from a legacy database into SQL Server 2000. These packages are currently within a development environment, but will need to be moved to a customer site at some point in the future. Within each of these packages are (obviously) connections to both source and destination databases. These connections will need to be changed when installed onto a given customer site. I believed that the easy way to do this would be to use a .UDL file for the SQL Server connection and an ODBC DSN for the connection to the legacy database (does not support OLEDB). However, if I make changes to the UDL file, these changes are not reflected when the package is next run (I assume some sort of caching is taking place?). Only when I re-open and then save the package, does it pick up the changes to the UDL file. The DSN works fine however, and the changes are reflected each time.To get around this I tried to change the OLEDB connections to connection tasks in the packages and change the values programmatically using VB - but again the same problem occured. These changes only take effect when the package is re-opened and saved. If this is the case, how can I easily alter all of the OLEDB connections when I install all of these packages to a customer site? Ideally I dont want to sit there and save every single package! And if the customer then made changes in the future they would have to repeat the same process! Hope this is a bit clearer? Cheers for any help :DGareth |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2003-11-07 : 07:38:38
|
| One option might be to use DTSGlobalVariables in an activeX script. These provide you the ability to pass variables, such as connection, userid, password, etc.., into your DTS packages. There are many good articles on this site regarding the use DTSGlobalVariables. |
|
|
|
gar3th_jon3s
Starting Member
4 Posts |
Posted - 2003-11-07 : 08:37:38
|
| Would using that method not mean I would have to have the same activeX script in every package though? Is there a way of storing a script in one place and re-using it in every package? The only way around it I can think of using a script is to create a COM object that reads the settings from a xml descriptor file or something.. and the script uses this to populate the global variables. I am amazed that the UDL isnt refreshed every time the package is run.. it is REALLY annoying.CheersGareth |
|
|
|
dshelton
Yak Posting Veteran
73 Posts |
Posted - 2003-11-10 : 02:04:52
|
| Have you tried to refresh the cache? |
|
|
|
gar3th_jon3s
Starting Member
4 Posts |
Posted - 2003-11-10 : 03:59:23
|
| If you mean the package cache, I have caching turned off.. |
|
|
|
|
|
|
|
|
Refreshing package details