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

More Portable DTS Packages Expand / Collapse
Author
Message
Posted Monday, April 4, 2005 10:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 6, 2005 11:47 AM
Points: 1, Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tdavid/moreportabledtspackages.asp


Post #171869
Posted Thursday, April 7, 2005 1:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, July 20, 2014 9:06 PM
Points: 2,693, Visits: 1,202

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?

 



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 #172722
Posted Thursday, April 7, 2005 1:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 29, 2008 5:10 AM
Points: 78, Visits: 43

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.

Post #172727
Posted Thursday, April 7, 2005 2:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:48 AM
Points: 430, Visits: 312

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:

 

Post #172755
Posted Thursday, April 7, 2005 11:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, January 30, 2006 11:22 AM
Points: 10, Visits: 1

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



Post #172953
Posted Thursday, April 7, 2005 12:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, May 4, 2014 10:21 PM
Points: 202, Visits: 307

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!




Post #172995
Posted Thursday, April 7, 2005 4:40 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, July 20, 2014 9:06 PM
Points: 2,693, Visits: 1,202

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.

 



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 #173063
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse