DTS Package portability

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

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!

Does anyone have any suggestions? What is the limit to command-line
length in WIN 2000?

Thanks!

[Simon Hayes]

Comments inline.

Simon

"Theodore Feldman" <th**************@fmglobal.com> wrote in message
news:26**************************@posting.google.c om...

If connection properties and global variables are set from an INI
file, how can a DTS package be made truly portable?
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.
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.
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 "environments".
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.
You can just pass the server name and database name of the "configuration
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.
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!
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.
Does anyone have any suggestions? What is the limit to command-line
length in WIN 2000?

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]

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!

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