sql server dts: Hello. This question was asked by "LucasC" about a year ago and no one
answered. I'm having the same issue now. To paraphrase LucasC:

"We are using a DTS that basically transforms data from a Table in one
repository to a Table in another repository using a DataPump Task.
We also has a success workflow and a failure workflow after the Data
Pump
Task (graphically it is linked to the destination Connection) in order
to
manage error and success (and log this results and change our internal
state
of the transaction).
If the transformation fails, the failure workflows is used (That's OK).

BUT if the destination connection fails (login, timeout, etc), the
failure
workflow is not used (and our error handling code is never call).

Is this by Design? Is there any parameter or property that allow us to
view
this connection error as part of the Transaformation and then make it
execute the failure workflow?"

My own DTS package loops through a set of connection attributes (server
and DB names). It is basically searching for the first connection at
which a certain set of criteria are met. When it finds that
connection, it stops looping and continues package execution.

If a set of connection attributes results in a "sql server does not
exist", "access denied", or any other kind of error once dynamically
applied, I want to be able to recover and move on to the next
connection, WITHIN the package. But like the poster I quoted above,
I'm not finding any way to trap that error withing DTS. Instead, a
generic DTS error is raised to the calling proc (I'm using xp_cmdshell
and dtsrun) and I'm forced to abort the whole process.

If it isn't possible to handle this kind of error in DTS (that's what I
suspect), I'll just have to wait until we upgrade and I can use SSIS.
I WILL be able to do this using SSIS, won't I??

TIA,
sonny

You all don't seem interested, but here is how I solved my problem.

I created a second package that takes as input the names of server and
DB to which my "master" package needs to connect. The second package
merely sets the connection dynamically and executes a generic SQL
statement.

The master package executes the second package via an ExecutePackage
task and has both SUCCESS and FAILURE workflows out of it. So now I
can control what happens in the master package, whereas before the
master would always fail and exit if it could not connect to the
dynamically-set server/DB.

I can also record all the instances of failed connections, which will
be useful for auditing.

sonny