|
|
|
SSC Eights!
      
Group: General Forum Members
Last Login: Friday, August 03, 2007 2:55 AM
Points: 928,
Visits: 1
|
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Friday, September 30, 2005 1:42 PM
Points: 31,
Visits: 1
|
|
The example given works fine. However, I have experienced issues with using the Dynamic Properties Task to set database connections in nested DTS Packages. Sometimes it worked, sometimes it didn't. With a complex environment, there is also a good opportunity to miss setting a connection that could cause large problems. I have found that storing UDL's local to the different machines worked more consistently for connections.
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 1,560,
Visits: 1,408
|
|
In my opinion, a DTS package is not portable unless the server from which it is moving can be taken completely out of the picture. This actually creates more overhead, in my opinion, because now you have a whole database that you have to copy over to a new server if you are moving a dts package and you have to manually edit the .ini file to satisfy complete removal of the other server. By his own admission, the author states that a requirment for his process to be successful is that "Be executed from a client machine as well as on the server – Does not reference any local files or connections.". Yet his whole process relies on the existence of a "Local Connection" withing the DTS Package that is pointing to a local mapped drive to the .ini file. So there are two local aspects in his equation and therefore does not satisfy his requirement.
My blog: SQL Soldier Twitter: @SQLSoldier Microsoft Certified Master: SQL Server 2008 Sr. Product Consultant and Chief SQL Server Evangelist @ Idera My book: Pro SQL Server 2008 Mirroring
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Tuesday, April 07, 2009 12:55 PM
Points: 34,
Visits: 3
|
|
We wrap our packages in what we lovingly call core scripts. They are VBScript that handle all the variables. We can literally port a package to any server at any time and the "core script" is smart enough to know where it's being executed from and change all variables accordingly.
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 1,560,
Visits: 1,408
|
|
I like that much better!! If you execute the DTS Packages via DTSRUN.exe, it is simple to pass in global variables as parameters that are set at run time within the VBS file.
My blog: SQL Soldier Twitter: @SQLSoldier Microsoft Certified Master: SQL Server 2008 Sr. Product Consultant and Chief SQL Server Evangelist @ Idera My book: Pro SQL Server 2008 Mirroring
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Today @ 2:22 AM
Points: 2,693,
Visits: 1,081
|
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Monday, June 30, 2008 8:10 AM
Points: 3,
Visits: 2
|
|
| Maybe I am missing something but I don't think anything can be called portable unless you can actually move it around. For example, a DTS package can actually "port" data over from one server to another but a DTS package itself cannot be moved from one server to another (atleast not in my experience.)
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Tuesday, April 07, 2009 12:55 PM
Points: 34,
Visits: 3
|
|
The package is moved from one server to another all the time by generating a script from dev, executing in qa, then executing in prod. The prod package still has all the settings from dev, but by using vbscript and rundts the variables in the package are overwritten at runtime by environmental variables (created and passed from the vbscript). So, if you execute the dts package I create in prod from enterprise manager or sql scheduler, it will use the defaults from dev and won't be successful. But, using Zeke and an app server to schedule and run the vbscript will work every time.
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Friday, September 30, 2005 1:42 PM
Points: 31,
Visits: 1
|
|
| We run all of our jobs in several environments and still use the SQL Server Agent or EM to run the jobs. The key is the synchronization of the DTS package ID's across the environments. We have DTS package that performs the elevation of changes by archiving previous version and then moving the records that store the packages from the MSDB database to the target machine. This way the package id remains the same for all environments and does not affect scheduled jobs or execution of packages from within EM. Instead of managing VBScript, we manage the UDL's and ini files local to the machines.
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Today @ 3:00 PM
Points: 1,560,
Visits: 1,408
|
|
Sure it can. I moved more than 100 DTS packages from one production server to another simply by moving the msdb database and then making a few edits to the syspackages table.
My blog: SQL Soldier Twitter: @SQLSoldier Microsoft Certified Master: SQL Server 2008 Sr. Product Consultant and Chief SQL Server Evangelist @ Idera My book: Pro SQL Server 2008 Mirroring
|
|
|
|