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


More Portable DTS Packages


More Portable DTS Packages

Author
Message
tdavid
tdavid
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tdavid/moreportabledtspackages.asp



philcart
philcart
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2752 Visits: 1434

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
Bill Geake
Bill Geake
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

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


Paul Smith-221741
Paul Smith-221741
SSC-Addicted
SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)

Group: General Forum Members
Points: 444 Visits: 334

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
augustin Carnu-215787
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

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


Jeremy Brown
Jeremy Brown
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 310

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
philcart
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2752 Visits: 1434

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
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