Post reply

DTSLookup error handling

  • April 18, 2006 at 1:22 pm

    #169048

    Hello all,

    Does any one know how to capture the sql error when the sql statement in the DTSLookup step fails?

    For example, I have an insert statement in my DTSLookup step. When this Insert statement fails for whatever reason, how can I capture the database error message?

    Any help would be appreciated.

    Kimberly.

  • April 18, 2006 at 1:27 pm

    #632953

    In DTS designer Click Package, Properties.  Set up loggin on the logging tab.  This should help captue the error.  Also you should be able to see what happened if it's a scheduled job by right clickign on job and clicking job history.  Put a check in the box for the show steps and click on the step that failed.  you should get ther error message there as well..

    -Luke.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • April 18, 2006 at 2:06 pm

    #632960

    Thank you for your reply.

    I do have this logging file defined in the package property as you said. However, the log said all the steps suceeded. (Besides the DTSLookup step (ActiveX script in Transformation), I also have the OnFailure step to handle the failure scenario. This OnFailure step if executed will log the error for the current row, skip the current row, and loop back to process the next row).

    So when my DTSLookup statement fails, my OnFailure workflow will kick off and I want to capture the database error of the failed sql statement in the lookup.

    Kimberly.

    P.S.: If I run my package manually: when the error occurs, the OnFailure step will be executed, and then loop back until all the rows in the record set is processed. Whether or not there's an error in any step, my package still run to completion. If there's an error, I double click on the red X of that failed step and got the message "Statement was terminated on line number 262". (this is where the DTSLookup was called.)

    If I manually execute the DTSlookup statement (the one that I know fails from the above step), I got a more descriptive error message from the database: "conflict foreign key constraint", and it tells me exactly what column has failed. Ideally this is what I want to capture and log in the OnFailure step.

    But if you or anyone know how to capture either message (DTS error message or database error message), I would really appreciate it.

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

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