Stored Procedure Return Values from TRY CATCH to Output Parameters in SSIS

• manser33

  SSC Veteran

  Points: 281

  More actions

  June 30, 2011 at 4:08 am

  #253652

  Hi All

  i need returne ErrorMessage,ErrorSeverity and ErrorState from try catch block in SP....:

  BEGIN CATCH

  DECLARE @ErrorMessage NVARCHAR(4000);

  DECLARE @ErrorSeverity INT;

  DECLARE @ErrorState INT;

  SELECT

  @ErrorMessage = ERROR_MESSAGE(),

  @ErrorSeverity = ERROR_SEVERITY(),

  @ErrorState = ERROR_STATE();

  RAISERROR (@ErrorMessage,

  @ErrorSeverity,

  @ErrorState)

  END CATCH

  I want to use them as output parameters in ssis sql execute task

  for example:

  http://www.bidn.com/blogs/BillBrannen/bidn-blog/612/stored-procedure-return-values-and-output-parameters-in-ssis

• TangoVictor

  SSChampion

  Points: 10976

  More actions

  June 30, 2011 at 8:11 am

  #1346563

  Hi, first off I am using an ADO.Net connection type

  I have created a sample procedure that errors out due to divide by 0

  CREATE PROCEDURE [dbo].[test]

  @ErrorMessage NVARCHAR(4000) OUTPUT

  ,@ErrorSeverity INT OUTPUT

  ,@ErrorState INT OUTPUT

  AS

  DECLARE @Div INT

  SET @Div = 0

  BEGIN TRY

  SET @Div = 6 / @Div

  END TRY

  BEGIN CATCH

  SELECT @ErrorMessage = ERROR_MESSAGE()

  ,@ErrorSeverity = ERROR_SEVERITY()

  ,@ErrorState = ERROR_STATE() ;

  END CATCH

  next I called this from an Execute SQL Task set up with output params (see screen shots)

  The param names in the Parameter Mapping match those from the stored procedure.

  this should get you going....

  tom

• manser33

  SSC Veteran

  Points: 281

  More actions

  July 4, 2011 at 1:07 am

  #1347644

  10x TOM:)