DTS - checking validity of connections

[drlex]

Hello all,
Apologies if this question has an obvious answer; I'm self-taught on SQL server and SQL, plus have no VB background, so may be missing the blindingly obvious.

I use SQL server as a Data Warehouse for subsequent processing. I have written DTS packages to perform daily and weekly updates, as well as log and back-up routines.

The source database is queried via an ODBC connection to an ODBC broker the Unix box on which it resides. Occasionally, the IT dept take the Unix box down and fail to restart the broker. The result is that SQL server cannot access the DB.

I'd like to set up a DTS package which tests that the ODBC connection works and not only emails me a warning, but also prevents any further DTS packages from running until the ODBC connection is restored.

I have cobbled together a VB-like script that can test the ODBC link using ADODB.Command which works with its application (Cognos), but can't determine how to wrap a DTS package around it - I get syntax errors on the first line.
The other approach I took was to try a test data transformation and on failure use the Send Mail task and a SHUTDOWN command, but the DTS package hangs on a RPC error when it can't access the source table, requiring operator input and will continue to run subsequent scheduled packages.

Any pointers most welcome.
(CDONTS not running on this machine)

lex

soi la, soi carre

 

[drlex]

Managed to fudge this one by using an ActiveX script, with Send Mail and SHUTDOWN tasks on failure. Script is:

dim objCmd
dim objRs
Function Main()
On Error Resume Next
Set objCmd = CreateObject("ADODB.Command")
objCmd.ActiveConnection = "DSN=UNISON63;UID=geno;PWD="
objCmd.CommandText = "SELECT prog-id FROM run-control"
Set objRS = objCmd.execute
If err <>3709 then Main = DTSTaskExecResult_Success
If err = 3709 then Main = DTSTaskExecResult_Failure
objRS.close
Set objRS = nothing
Set objCmd = nothing
End Function

soi la, soi carre