I didn't see this, but assumed it would be a very common question.

We have many SQL servers and in some cases the DTS jobs copy selected data
from ServerA to ServerB then lots of connection tasks pointing to ServerB.
In most cases changing the name of ServerB to (local) would work, but not in
all cases. It certainly doesn't help much that ServerA is coded frequently.

We migrate our DTS fro environment to environment another individual, prior
to leaving the company, indicated he had changed ALL connections to read in
Global Variables from a locally maintained DTS.INI he built.

Well, we have new servers to test our processes on and I'm now getting into
the guts of the DTS and finding out the Global Variables trick is on;y being
picked up and used by 1 or 2 connections. The rest are falling back onto
whatever was hadcoded, clearly not what we want.

Is there a way to control all connection values from a single place per DTS
job?

You could use a Dynamic Properties task to modify the properties of all
of your connections as the first step in the package. That task could
read out of an *.ini file, or you could make the assignment by global
variables which could be passed in at the time that the DTS package is
executed (use dtsrunui.exe to see how to call a DTS package and pass in
the values for the global variables).

This is what is already being done. It works fine for the 'Execute SQL
Tasks' tool, but not the 'Microsoft OLE DB Provider for SQL Server' tool or
the 'Copy SQL Server Objects' tool which don't seem to support global
variables (at least I haven't been able to expose them).

doug


"Tim" <mitsirrah (AT) netzero (DOT) net> wrote

Okay, reading more. It would appear the objects I'm concerned about don't
have design time properties that can be exposed, but the Dynamic Properties
Task can still be used to change their properties at run time if you add
them to the list. Going to give it a try.


"doug" <dsmrtn (AT) pacbell (DOT) net> wrote

Hummm... I'm still researching, but what I have determined so far is that
the Dynamic Properties Task IS ALREADY setting the properties for ALL
connections in the job, but at runtime, it "appears" to be using the value
on at least 1 connection to access the tables on the server that is
hardcoded in the design time object. I iknow this because we use a table to
control many external parms (a name/value lookup table) like date parms.
Current date would be a value of '0'. We accept offsets (-1, -2, or
specific dates like '20050311'. I overrode the table on the local server to
use a hardcoded date, ran the DTS job and received the errors you get when
logic gets value from the table, and if NOT a dfarte, but an realtive date,
it tries to add the offset amount to current date then do date compare edits
on rest of data. It was retrieving a value of +0 from the wrong server...
DTS says it is in fact supposed to use the INI file to plug in the name of
the local server, and it didn't behave that way.

I'm not sure, but think I'm worse off now than I was originally...now it
appears "code" right, behavior is wrong.


"doug" <dsmrtn (AT) pacbell (DOT) net> wrote

Have any tasks been added to the DTS package that don't wait for the
dynamic properties task to be run first?

Tasks may have been added, but all tasks depend on the dynamics property
task.

I am only able to recreate it intermittently.

The log file lists the dyntamic properties task last, seeminly always, even
on the rare cases where the problem seems to show up (using what i think is
the design time value).

doug


"Tim" <mitsirrah (AT) netzero (DOT) net> wrote