Looking for info on handling error with script.

  • I have a package that uses a for-each loop to connect to a list of servers to transfer various pieces of data for DBA administration stuff from the servers to a central database we use for reporting. It has been failing and when I looked at it I found that while it has error handlers on each of the data flow objects, and it does log error info about failing connections, it's not handling the errors correctly to keep the entire package from failing.

    My solution was to add a script and a sequence container. The script, (thanks Jamie Thomson) takes the connection for that particular loop and tests it to see if the server is available and writes a record to the error table if it is not. I am also able to extract the exact connection string and insert it into that record. That should allow me to skip the sequence container if my script fails to make a connection to a given DB server. It does work, but as I run it in debug it turns the script red, as in failure, and continues on, and in the end all of the data flow objects end up as failures. I am trying to figure out if I can make this more elegant. While no one would see this in normal execution, it sure looks like something is failing when in debug mode and I doubt it will get better when running in a job.

    What I would like to do, if possible, is to remove the task failure from the script, which it currently does if it cannot make a connection to a given server, have the script allow the sequence container to execute if a connection is successful and just skip it if not, The script already logs the error info so I just need to move to the next connection.

    Is this possible?

    Thanks,

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

  • use C# script task to do the work.

    trap the error in a loop and ignore it.

  • I have a VB script now, and I could ignore the error, but I'm trying to use the script to decide whether to run the objects in the sequence container, because if the connection can't be made by the script I want it to skip the sequence container so I don't have a bunch of data flow objects failing if the target server is not available.

    Thanks,

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

  • Sorry perhaps I wasn't clear. Use script to make your connections also. Do not use a component for anything. Then you will have no trouble handling an error from a connection isssue in any way you like.

    ken

  • Stamey (8/17/2012)


    I have a package that uses a for-each loop to connect to a list of servers to transfer various pieces of data for DBA administration stuff from the servers to a central database we use for reporting. It has been failing and when I looked at it I found that while it has error handlers on each of the data flow objects, and it does log error info about failing connections, it's not handling the errors correctly to keep the entire package from failing.

    My solution was to add a script and a sequence container. The script, (thanks Jamie Thomson) takes the connection for that particular loop and tests it to see if the server is available and writes a record to the error table if it is not. I am also able to extract the exact connection string and insert it into that record. That should allow me to skip the sequence container if my script fails to make a connection to a given DB server. It does work, but as I run it in debug it turns the script red, as in failure, and continues on, and in the end all of the data flow objects end up as failures. I am trying to figure out if I can make this more elegant. While no one would see this in normal execution, it sure looks like something is failing when in debug mode and I doubt it will get better when running in a job.

    What I would like to do, if possible, is to remove the task failure from the script, which it currently does if it cannot make a connection to a given server, have the script allow the sequence container to execute if a connection is successful and just skip it if not, The script already logs the error info so I just need to move to the next connection.

    Is this possible?

    Thanks,

    Chris

    In your script, you can control its apparent success or failure via Dts.TaskResult = ?, which I am assuming you have no problem with.

    What you could also do is create an int package variable - call it 'Success' or whatever - and set its value in the script. 0 = failure and 1 = success.

    Then, use precedence constraints after your script task to direct the program flow, depending on the value of 'success'.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 5 posts - 1 through 4 (of 4 total)

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