More Portable DTS Packages

Question

More Portable DTS Packages

tdavid

Grasshopper

Points: 19
More actions
April 4, 2005 at 10:43 am

#165056

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

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?

--------------------
Colt 45 - the original point and click interface

Bill Geake Ten Centuries Points: 1352 More actions · Answer 2

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.

Paul Smith-221741 SSCrazy Points: 2003 More actions · Answer 3

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

Section

ParameterName

ParameterValue

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

augustin Carnu-215787 SSC Enthusiast Points: 192 More actions · Answer 4

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?

Cheers,

Augustin Carnu

Jeremy Brown SSCarpal Tunnel Points: 4223 More actions · Answer 5

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!

philcart SSC-Forever Points: 47796 More actions · Answer 6

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.

--------------------
Colt 45 - the original point and click interface