Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Portable DTS Packages


Portable DTS Packages

Author
Message
Jonathan Stokes
Jonathan Stokes
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1053 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!
Paul Krempec
Paul Krempec
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 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.


Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2422 Visits: 1623

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 MVP
Database Engineer at BlueMountain Capital Management
SueStill
SueStill
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 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.



Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2422 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
philcart
philcart
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3582 Visits: 1436

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
Munjal Subodh
Munjal Subodh
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.)
SueStill
SueStill
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 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.



Paul Krempec
Paul Krempec
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 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.
Robert Davis
Robert Davis
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2422 Visits: 1623
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 MVP
Database Engineer at BlueMountain Capital Management
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search