Post reply

Problem with trapping errors

  • February 17, 2004 at 11:11 am

    #59579

    Hi,

    I have a package that runs procedures on 4 distinct servers and when all have completed kicks off a procedure on a central machine.  The problem I have is that sometimes the newtowrk isn't what is should be and one of the steps fails because it can't connect to the remote server.

    What I want to do is to use the ON error workkflow, to insert a row on the central server with the name of the connection that has failed.  Is there a variable that I can use that denotes the connection that has failed, which I can use within a SQL statement?

    Ideally I would like to email the details of the failed connection, but an insert into log table will suffice for now.

  • February 17, 2004 at 11:37 am

    #494413

    Create an execute sql task that executes on error.  You would have a seperate one for each connection.

    Set it to use the connection where you will be writing your error.  You will need to have a separate task for each connection.  You could hard code the server name here and by pass the

    exec cp_LogError 'MyServer'

    Russel Loski, MCSD

     

    Alternatively you could use an active x script that executes on error.  It gets the data source from the connection.  Then follow it with a parameterized Execute SQL task.

    Note: you will need to find out the number of the connection which gave the error.  Since this is an error from a task that used connection 2, I use 2.

    Second note:  if you have more than one task that can error, you will need more than one ActiveX script task (pointing to different connections).  This is important also, because you will need to put the name of the datasource into its own global variable, otherwise if two servers error out at once, one activex script will set the global variable and the next one will set it to something different.

    '**********************************************************************

    '  Visual Basic ActiveX Script

    '************************************************************************

    Function Main()

    DTSGlobalVariables("FailedServer2").Value =  DTSGlobalVariables.Parent.Connections(2).DataSource

     Main = DTSTaskExecResult_Success

    End Function

    Russel Loski, MCSE Business Intelligence, Data Platform

  • February 18, 2004 at 3:54 am

    #494487

    Thanks Russel.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply