handling abnormal termination of underlying stored proc

• etl2016

  Default port

  Points: 1459

  More actions

  March 2, 2016 at 9:46 am

  #305226

  Hi,

  I have a scenario SSIS --> Stored Proc.

  Is there a way to error handle in such a way that if SP fails (abnormally), SSIS should gracefully exit, instead of failing as well

  could you please advise

  thanks

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  March 2, 2016 at 10:03 am

  #1862475

  etl2016 (3/2/2016)

  ---

  Hi,

  I have a scenario SSIS --> Stored Proc.

  Is there a way to error handle in such a way that if SP fails (abnormally), SSIS should gracefully exit, instead of failing as well

  could you please advise

  thanks

  Yes add another step and attach it to the step that executed the stored proc. Then right click the green arrow and change it to ON ERROR.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Phil Parkin

  SSC Guru

  Points: 246959

  More actions

  March 2, 2016 at 10:25 am

  #1862482

  etl2016 (3/2/2016)

  ---

  Hi,

  I have a scenario SSIS --> Stored Proc.

  Is there a way to error handle in such a way that if SP fails (abnormally), SSIS should gracefully exit, instead of failing as well

  could you please advise

  thanks

  If there is a particular error which you want to handle gracefully, while allowing other errors to fail the package, you should be able to do that via careful use of TRY-CATCH blocks in your proc.

  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

• etl2016

  Default port

  Points: 1459

  More actions

  March 2, 2016 at 10:44 am

  #1862496

  Thank you.

  I am implementing this in PDW/APS and TRY CATCH is not supported. I can flush a predefined error message from SP and look for that in SSIS, will try

  Thanks