June 2, 2004 at 8:38 am
I have been moving DTS packages between Servers without a problem as long as I remember to set the Server to [Local].
I now however have hit a gotcha when trying to run the packages on a new instance because [Local] references the default server not the instance where the package is installed.
I have seen reference in other threads to using an INI file or using dtsrun with /A parameter.
I have not used either of these before. But reading up on INI I can't see how this helps as if I have a default instance and a second instance on the same server wouldn't this mean that I have to amend every DTS Package to point to a different INI file. In which case it would be easier to manually change the source.
Are there any examples anywhere other than BOL which would help me use INI or DTSRun to change the Data Source.
June 2, 2004 at 2:09 pm
If you are running SS2K, you may want to read up on using the Execute Package task and the Dynamic Property task. How we do this is we specify in the package's global variables two variables, gvServer and gvDatabase. The first task that runs is a Dynamic Properties task and it sets the Server and Catalog properties of the packages' Connection objects. This way, if you port the package to another server, all you need to do is change the two global variables and you are set to run on this server. If you have a series of packages that run, you can accomplish the same thing and in the first package you execute, it can call the next package using the Execute Package task passing Outer Package Variables, which would be your server and database global variables.
Lee
June 2, 2004 at 2:50 pm
If I have say 20 independent packages, does this mean that I have to change 20 lots of global variables every time I move the packages to a new instance.
June 2, 2004 at 3:02 pm
Not necessarily. You could use an INI file or something like it to read in the two variables you need and then use the Dynamic Task property. The trick however is to get the variables and use the Dynamic Task property. Where you get the variables from is up to you and what works best in your environment.
June 3, 2004 at 2:34 am
I ended up programming something in C# to use the DTS COM object to copy a package from one server to another and programmaticly search through all the properties in the packages for the origin server name and replace it with the destination server name. I have 8 DBs that I admin that are scattered all over US/Canada and with one click of the mouse they get copied and revised. Much better than any other suggestion I've seen.
June 3, 2004 at 2:43 am
"I ended up programming something in C# to use the DTS COM"
Unfortunately I can not program in C#.
"You could use an INI file or something like it to read in the two variables"
As tne second instance is on the same PC as the default instance any path embedded in the 20 packages would point to the same INI file. The only solution I can think of then is to have two templates for the ini file and copy these over the top of the target ini file before running any package.
Does this sound the best solution?
I need to find out how to use an INI file, any pointers to setting this up to hold a data source?
June 3, 2004 at 5:55 am
Found the answer re how to use INI files - Excellent tutorial:
"Updating SQL Server Connection Properties from the DTS Package INI File"
http://www.databasejournal.com/features/mssql/article.php/3073161
The next step I guess is to create another package which will copy the template (ActiveX script using the FileSystemObject) over the top of config.ini.
I can run this package once before running any other packages. The only issue I have is to make sure that when I run these packages as jobs that I make sure the default instance and the new instance are timed so they do not run at the same time.
I will be pleased to hear if anyone has a better way of doing this.
June 3, 2004 at 8:01 am
Other than programming your own external solution, I doubt you're going to find any sort of solution that doesn't involve your manual intervention to make each set of packages work on different servers. I spent a good two month despeately seraching before I gave in. You dont need to use C# specifically since DTS is a COM object.
June 4, 2004 at 4:13 pm
Check the options on DTSRun. You can pass arguments to specify the server instance if you choose to do so; also can pass in global variable values which can be used with Dynamic properties to specify the connections.
June 4, 2004 at 4:23 pm
DTSRun isn't going to help me if I need to run a DTS Package on a server in Los Angeles and i'm in New York and I dont want to run the package from the new york server.
August 25, 2004 at 12:04 pm
This is directed to Vince... I know this is a bit of time after this general thread was going on, but have been looking for a way to programmatically change connection info in a DTS package and I like your approach - would you be willing to share your C# code? I have VS.Net2003 so code should be compatible. Cheers, Dennis M.
maddog
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply