DTS Package portability

**Simon Hayes** · Jul 20 '05, 12:50 AM

Re: DTS Package portability

Comments inline.

Simon

"Theodore Feldman" <theodore.feldm an@fmglobal.com > wrote in message
news:26032dec.0 307070705.18444 ed0@posting.goo gle.com...[color=blue]
> If connection properties and global variables are set from an INI
> file, how can a DTS package be made truly portable?[/color]

You don't have to use an INI file - you can use other sources, including a
database query, which is often the most flexible and powerful.
[color=blue]
> If the INI file pathname is hard-coded in the package, it must be
> edited when you copy the package to a new environment; otherwise your
> packages in multiple environments will all reference the same INI
> file. If there are dozens of properties that need to be set
> dynamically (for example, connections that reference different text
> files), changing the pathname of the INI file for each one can be a
> real bear.[/color]

You could store these in a database table, along with some key values such
as site code, package name etc. to identify which set of values are to be
used in which "environmen ts".
[color=blue]
> I was hoping to be able to set the pathname of the INI file from the
> command line as a global variable. Then the package could be pointed
> dynamically to one of several INI files. However, the pathname for
> the INI file cannot be set dynamically from a global variable, as far
> as I can tell.[/color]

You can just pass the server name and database name of the "configurat ion
database" from the command line, then retrieve the rest of the values from
the parameters table, based on whatever criteria are most useful in your
situation.
[color=blue]
> One alternative would be to set connection properties and global
> variables from the command line. Again, if there are dozens of
> connections, the command line will be quite long![/color]

Probably better to pass the minimum number of values on the command line and
retrieve the rest from some other location. Using database tables makes
centralized management and logging much easier.
[color=blue]
> Does anyone have any suggestions? What is the limit to command-line
> length in WIN 2000?[/color]

No idea, but as you mentioned, the command line is quite limiting. If you
really had to do this, it would probably be easier to call the DTS package
and set the variables from VB, using some sort of config file (back to INI
files again), rather than cram everything onto the command line.

**takahe** · Sep 13 '05, 03:35 PM

Originally posted by Theodore Feldman

If connection properties and global variables are set from an INI file, how can a DTS package be made truly portable?

If the INI file pathname is hard-coded in the package, it must be
edited when you copy the package to a new environment; otherwise your
packages in multiple environments will all reference the same INI
file. If there are dozens of properties that need to be set
dynamically (for example, connections that reference different text
files), changing the pathname of the INI file for each one can be a
real bear.

I was hoping to be able to set the pathname of the INI file from the
command line as a global variable.

Totally agree with the sentiment. I had a similar problem.

What I have done is to create a Dynamic Properties task which sets all of the variables that I want to populate from an ini file. In the Dyn Props task I specified a valid 'hard coded' ini file name, since as you say it cannot be entered as a parameter.

BUT, at runtime it becomes dynamic! This is achieved by creating an activex script task which is executed prior to the Dynamic properties task and whose job it is to set the actual ini file values in the Dyn Props task at run time for any assignments which are INI file based. This can then be set to a global var, environment var or whatever you want.

The text of my activex script is below
Hope it helps!

Code:

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************



Function Main()

	'----
	' Get the MY_INI_FILE environment variable
	'----

	Dim shell, env
	Set shell = CreateObject("WScript.Shell")
	Set env = shell.Environment("process")

	iniFileName=env("MY_INI_FILE")
	'---
	' nb. we could equally have set iniFileName from a variety of places, such as
	' a global var for example, which could be passed in on the command line to dtsrun:
	' e.g. replace the above lines with:
	'iniFileName= DTSGlobalVariables("MY_INI_FILE")
		

	'----
	' Get the package object so that we can get a handle to our dynamic properties task
	'----

	Set oPkg = DTSGlobalVariables.Parent
	Set oDynProps = oPkg.Tasks("DTSTask_DTSDynamicPropertiesTask_2").CustomTask

	'----
	' iterate through the dynmic property assignments for the selected dynprops task
	'----
	for each oAssign in oDynProps.assignments

		if oAssign.sourceType = DTSDynamicPropertiesSourceType_IniFile then

			'----
			'whenever the source type is an ini file, change the file name
			'----
			oAssign.SourceIniFileFileName  = iniFileName
		end if
	next
	
'----
'Return Success
'----

	Main = DTSTaskExecResult_Success
	
End Function