Change Server inside the DTS Package
-
When I moved a DTS Package from development server to production server, I used Enterprise Manager, opened the package in development and saved under the production server. Then I had to go to the production server, opened the package and changed to production server inside the DTS Package. I put in a Dynamic task properties in the DTS Package. It mapped to the server properties under Connection. It supposes to read a table to get the right server so I don't have to open up the DTS Package to change the server everytime I migrate. However it did not work. The DTS Package read a table from Oracle and loaded into SQL Server. I can use the Dynamic property to change the Oracle server but not the SQL Server. Can someone know how to change the SQL server inside the DTS Package without opening up the package and change it manually?
Thanks
-
This is a flaw in DTS. It's a chicken & egg situation - reading configuration from a table requires a connection. However, you need the connection set to the correct environment to connect to the correct Table, but only the Table contains the correct info. Can't have the correct connection without the table, can't query the correct table without the connection.
-
Perhaps one thing you can do is create a global variable to hold the server name (or IP). The dynamic properties task can use the value in the global variable to set the connection. Then you can set the global variable when you execute the package. You could then do away with the table. Or, you can get more complicated and have another process query the table to get the server and then call the package while setting the global variable.
-
This is all down to the initial package design.
What I have is a single table that stores all the global variables for my packages. These are global variables for various uses in each package, Server connections included. When I execute the package via DTSRUN, I pass in appropriate global variables to provide a connection to the server that this table resides on. The first few steps of the package, connect to this table and read in the global variables. Then connection properties are assigned using the dynamic properties task.
Take a look at this article,
http://msdn.microsoft.com/library/en-us/dnsql2k/html/sql_busintbpwithdts.asp
A simplification of the technique I use is outlined in the section titled "Metadata-Driven Approach"
The beauty of this approach is that I can move my packages from server to server and all I have to change is the global variables in the table and the ones on the command-line. No DTS editing involved.
--------------------
Colt 45 - the original point and click interface
-
Try to use Server Compare
The Server Compare application provides a simple and quick way to compare and synchronize two SQL server’s instances. You can compare server configuration parameters and other server objects such as logins, jobs, linked servers, and DTS packages. The application generates the T-SQL synchronization script based on the compare results, and can be run immediately, opened in Toad, or saved to disk. A history of all synchronizations with detailed reports and rollback scripts is available in this tool.
Supported servers: MS SQL Server 7.0, 2000, 2005 beta (Yukon).
http://www.toadsoft.com/ServerCompareBeta.zip
http://www.toadsoft.com/ServerCompareDemo.exe (flash demo)
-
Phil
Thanks so much. I took your approach and it worked liked a charm.
Janet
-
I have used Phil's approach in my packages as well. I indicate the server via the dtsrun command. Using that table driven method, I pull in paths for source files, distribution lists, and also the path to the package level ERR file which I build dynamically using an activeX task like this:
'This uses the value from a global variable to dynamically set the path to the error file
Function Main()
Dim DynamicLogFileName, Package
set package = DTSGlobalVariables.Parent
DynamicLogFileName = DTSGlobalVariables("ErrorFilePath").value
package.logfilename = DynamicLogFileName
Main = DTSTaskExecResult_Success
End Function
I implemented this method in all my packages. There was a lot of initial setup but it works well and makes it easy to move my packages from the DEV server to the PROD server. Since my paths are table driven, I have the DEV tables pointing to DEV locatations and the PROD tables pointing to PROD locations and I don't have to worry about forgetting to change one of the zillion paths.
I hope I am wrong about this but did anyone else read that the new SQLServer version is shutting off access to the Package global variables via ActiveX? This would make me sad and cause me to do a lot of maintenance.
Teague
-
The configuration in SQL Server Integration Services (DTS 2005) is handled much differently. You have a package configurations collection that you can play with instead of global variables.
--------------------
Colt 45 - the original point and click interface -
Server Compare looks like an interesting start. At the moment it's a bit limited in it's functionality.
I tried a few DTS compares that failed miserably and trying to filter out items that I didn't want to sychronise was almost impossible.
--------------------
Colt 45 - the original point and click interface -
A initialization file can be used where the server names can be specified , production server or Dev server.
A .ini file is very helpful while moving the packages across the Servers.
Using the Dynamic properties task , the servernames can be set. So there is no need to touch the DTS package when servers change
Hope this approach also helps
Regards
Meghana
-
True, an INI file also works, but with 2 limitations - you need to have the same hard-coded filesystem location to the INI file available on all environments. It also fails to handle situations of multiple named SQL instances running on the 1 server - for example if you are running DEV and QA on the same server box, but under different instances, how do you have different DEV/QA parameters if they're both reading the same INI file.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply