Retrieving the Error Type in the Data Flow

• tim.ffitch 25252

  SSCrazy

  Points: 2055

  More actions

  February 6, 2018 at 9:27 am

  #403093

  I have a flat file loading process that first loads to a staging table and then we process updates into the database from there. When and if this loads fail it is usual one of 2 things. Either there is a problem with the file itself or there is a database deadlock.

  What I want to be able to do is intercept the error type in the data flow, if it is a deadlock then I want the package to error as usual and let the retries on the SQL Agent Job run as usual in the hope that the deadlock is gone on the subsequent retries. If the error is not a deadlock, then I want to move the file into a failed folder so the SQL Agent Retry will kick in and the next file in turn will be processed.

  Has anyone got any ideas how I can intercept the error type to help me build this deign pattern.

  Thanks
  Tim.

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  February 6, 2018 at 11:29 am

  #1978746

  tim.ffitch 25252 - Tuesday, February 6, 2018 9:27 AM

  I have a flat file loading process that first loads to a staging table and then we process updates into the database from there. When and if this loads fail it is usual one of 2 things. Either there is a problem with the file itself or there is a database deadlock.

  What I want to be able to do is intercept the error type in the data flow, if it is a deadlock then I want the package to error as usual and let the retries on the SQL Agent Job run as usual in the hope that the deadlock is gone on the subsequent retries. If the error is not a deadlock, then I want to move the file into a failed folder so the SQL Agent Retry will kick in and the next file in turn will be processed.

  Has anyone got any ideas how I can intercept the error type to help me build this deign pattern.

  Thanks
  Tim.

  Which version of SSIS are you running? Things changed in 2016 to make this task easier.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• tim.ffitch 25252

  SSCrazy

  Points: 2055

  More actions

  February 7, 2018 at 7:36 am

  #1978885

  Phil Parkin - Tuesday, February 6, 2018 11:29 AM

  tim.ffitch 25252 - Tuesday, February 6, 2018 9:27 AM

  I have a flat file loading process that first loads to a staging table and then we process updates into the database from there. When and if this loads fail it is usual one of 2 things. Either there is a problem with the file itself or there is a database deadlock.

  What I want to be able to do is intercept the error type in the data flow, if it is a deadlock then I want the package to error as usual and let the retries on the SQL Agent Job run as usual in the hope that the deadlock is gone on the subsequent retries. If the error is not a deadlock, then I want to move the file into a failed folder so the SQL Agent Retry will kick in and the next file in turn will be processed.

  Has anyone got any ideas how I can intercept the error type to help me build this deign pattern.

  Thanks
  Tim.

  Which version of SSIS are you running? Things changed in 2016 to make this task easier.

  Funnily enough it is 2016. I have just begun to experiment with the OnError events and using a script task to see what information I can obtain from the error.

  What else do you have in mind?

  Thanks
  Tim.

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  February 7, 2018 at 7:53 am

  #1978895

  tim.ffitch 25252 - Wednesday, February 7, 2018 7:36 AM

  Phil Parkin - Tuesday, February 6, 2018 11:29 AM

  Which version of SSIS are you running? Things changed in 2016 to make this task easier.

  Funnily enough it is 2016. I have just begun to experiment with the OnError events and using a script task to see what information I can obtain from the error.

  What else do you have in mind?

  Thanks
  Tim.

  I was referring to the use of a Script Component (not Task) to retrieve Error Column Name and Error Column Description, as described here. It might help, if you need to be very granular.
  But I think that you are on the right track with your OnError research.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• tim.ffitch 25252

  SSCrazy

  Points: 2055

  More actions

  February 7, 2018 at 9:37 am

  #1978923

  Phil Parkin - Wednesday, February 7, 2018 7:53 AM

  tim.ffitch 25252 - Wednesday, February 7, 2018 7:36 AM

  Phil Parkin - Tuesday, February 6, 2018 11:29 AM

  Which version of SSIS are you running? Things changed in 2016 to make this task easier.

  Funnily enough it is 2016. I have just begun to experiment with the OnError events and using a script task to see what information I can obtain from the error.

  What else do you have in mind?

  Thanks
  Tim.

  I was referring to the use of a Script Component (not Task) to retrieve Error Column Name and Error Column Description, as described here. It might help, if you need to be very granular.
  But I think that you are on the right track with your OnError research.

  Thanks for the info, I didn't know about that, it could prove very useful.