Testing connection to DSN

• pankaj-426531

  Ten Centuries

  Points: 1123

  More actions

  April 24, 2007 at 4:29 am

  #70013

  We have a DTS that connects to a teradata server using a System DSN.

  The issue is that the password for the ID used in the connection expires after every 2 months.

  Whenever this happens, the DTS doesnt complete and hangs up.

  We want to put in a check at the start of DTS to check if the connection succeds and if it fails, the DTS needs to fail.

  We need pointer as to how we can achive this

• Adrian Nichols-360275

  SSCertifiable

  Points: 7694

  More actions

  April 24, 2007 at 6:18 am

  #702137

  You could use an ActiveX script to try to connect to the DSN. You can then have OnSuccess and OnFailure workflows from it.

  Something like:

  Function Main()

   Dim conn

   Dim connStr

   Dim userID, pWord

   userID = DTSGlobalVariables("gv_User").Value

   pWord = DTSGlobalVariables("gv_Pass").Value

   Set conn = CreateObject("ADODB.Connection")

   connStr = "DSN=trialDsnConnect;Uid=" & userID & ";Pwd=" & pWord & ";"

   conn.open (connStr)

   Main = DTSTaskExecResult_Success

  End Function

  This script can be modified with Error Trapping to customise error messages. Or, as said above, you can use Success and Failure workflows.

  ---

  
  Ade

  A Freudian Slip is when you say one thing and mean your mother.
  For detail-enriched answers, ask detail-enriched questions...[/url]