Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Portable DTS Packages Expand / Collapse
Author
Message
Posted Thursday, July 7, 2005 11:59 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, August 3, 2007 2:55 AM
Points: 928, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cherring/portabledtspackages.asp


------------------------------
The Users are always right - when I'm not wrong!
Post #198264
Posted Monday, July 25, 2005 10:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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. 

Post #204054
Posted Monday, July 25, 2005 11:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:16 AM
Points: 1,618, Visits: 1,554

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
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #204082
Posted Monday, July 25, 2005 11:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 7, 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.


Post #204084
Posted Monday, July 25, 2005 11:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:16 AM
Points: 1,618, Visits: 1,554
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
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #204085
Posted Monday, July 25, 2005 4:17 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, December 15, 2014 7:14 PM
Points: 2,693, Visits: 1,223

Take a look at the series of posts I'm putting together on my blog about a very similar technique.

 



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #204242
Posted Tuesday, July 26, 2005 5:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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.)
Post #204377
Posted Tuesday, July 26, 2005 8:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 7, 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.


Post #204441
Posted Tuesday, July 26, 2005 8:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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.
Post #204455
Posted Tuesday, July 26, 2005 10:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 8:16 AM
Points: 1,618, Visits: 1,554
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
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #204527
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse