SQLServerCentral / Discuss Content Posted by Tito David / Article Discussions / Article Discussions by Author / More Portable DTS Packages / Latest Posts

RE: More Portable DTS Packages

philcart — Thu, 07 Apr 2005 16:40:00 GMT

Ok, I'm confused, why can't I assume that an ActiveX Script task within the package won't run?

Our DTS packages move from various developer workstations to a QA server, to a test server, to one of 3-4 production servers, all without a hiccup.

RE: More Portable DTS Packages

Jeremy Brown — Thu, 07 Apr 2005 12:32:00 GMT

DTSRun from a command line is extremely limited. If you have many variables to pass in to a DTS package, you have to use a script / program.

I wrote an article posted earlier this week about creating the DTS package from scratch using Perl. In the article, I passed in several values from the command line using Getopt::Std. This functionality can be easily replaced by having Perl read a text (ini) file or get values from a database table.

You can also use a script to open an existing DTS package, assign the variables directly, and execute the package containing the dynamic properties task.

There are many ways to skin the DTS portability cat.

Nice job on the article!

RE: More Portable DTS Packages

augustin Carnu-215787 — Thu, 07 Apr 2005 11:09:00 GMT

You are missing the point... He's not talking about dtsrun.....and what happens in 'YOUR' approach when you have to update the package? What about copying the DTS to another (external) server where you have no access - when you cannot assume that your ActiveX Script will run?

RE: More Portable DTS Packages

Paul Smith-221741 — Thu, 07 Apr 2005 02:55:00 GMT

We use a 2 phsed approach to handle this

each SQL server has a single ini file in the root folder of the System drive. This contains 1 [Section] that contains the datasource=, catalog= (we use integrated security only) to point to a table on a server containing the rest of the information.

That table contains 3 columns to in effect work like an ini file

SectionParameterNameParameterValue

I will be recommending a change though from using the C: Drive to using a DFS root of the form \\DFSROOT\SQLPARAMS\@@SERVERNAME:

RE: More Portable DTS Packages

Bill Geake — Thu, 07 Apr 2005 01:30:00 GMT

Interesting, but the ini file problem (limited amount of data that can be held) can be got around (IME) by using a number of different sections in the ini file, and keeping each section below the critical size. This helps organisation, too. Ini files can then be held in a fixed location on each server, so the package can be set to look for its file at \\@@servername\dtsini\ or whatever.

Bill.

RE: More Portable DTS Packages

philcart — Thu, 07 Apr 2005 01:19:00 GMT

Nice article. A little different than the technique I use but effective all the same.

My table includes the package id as well and holds all the global variables for a package. From DTSRUN, I pass in a set of global variables to set the connection properties for the GlobalVar connection. Then an ExecuteSQL task extracts all the variables to a resultset and an ActiveX Script task dynamically creates them all.

The advantage of this approach is that my global variable table can reside in one location and serve multiple production servers. It also makes for an easy centralised maintenance location.

Also, I see you use a Dynamic properties task to set the Server Name for the GlobalVarsTable connection. What about the database name and login details?

More Portable DTS Packages

tdavid — Mon, 04 Apr 2005 10:43:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tdavid/moreportabledtspackages.asp