SSIS and how to capture ERROR message from a nested child stored procedure

Hi,

I'm using SQL SERVER 2008R2, Visual Studio 2008 for SSIS.

When the parent stored procedure usp_TEST_PARM  calls a child stored procedure and there’s an error in the child stored procedure, how do we get the child stored procedure to RETURN  a value so that SSIS would know that an error occurred in the child stored procedure (so that SSIS can send an alert email that says that an error happened in the child stored procedure and not the parent stored procedure usp_TEST_PARM  )?

GO

/****** Object:  StoredProcedure [dbo].[usp_TEST_PARM]    Script Date: 03/08/2018 16:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*

EXECUTE dbo.usp_TEST_PARM 'parm1'

*/

CREATE PROCEDURE [dbo].[usp_TEST_PARM]

@PASS_PARM as varchar(50)='Opps'

AS

SET NOCOUNT ON

SELECT [Field1]='Hello',[Field2]=@PASS_PARM
UNION
SELECT [Field1]='Hello2',[Field2]=@PASS_PARM
UNION
SELECT [Field1]='Hello3',[Field2]='Seconds'

RETURN 3

SET NOCOUNT OFF


GO


/*
to run it:

declare @var1  int
EXEC @var1 = dbo.usp_TEST_PARM 'parm1'
SELECT 'RETURN STATUS ' = @var1
GO
-------------------
RESULT:

Hello	parm1
Hello2	parm1
Hello3	Seconds

----------------------
RETURN STATUS 
3

*/

Open in new window

paultran00Asked:
Who is Participating?
 
Máté FarkasDatabase Developer and AdministratorCommented:
This should be managed in main stored procedure level and but in SSIS.
But it is possible to implement it only in SSIS.
Let's consider the following example:
CREATE PROCEDURE dbo.ChildProc
AS
SELECT 1/0 AS Result
GO

CREATE PROCEDURE dbo.MainProc
AS
EXEC dbo.ChildProc
GO

EXEC dbo.MainProc

Open in new window


You get the following error message from SQL Server when you execute procedure dbo.MainProc:
Msg 8134, Level 16, State 1, Procedure dbo.ChildProc, Line 3 [Batch Start Line 11]
Divide by zero error encountered.

Open in new window


So you get back the name of child procedure which raised the error. You can use this information in SSIS.
0
 
paultran00Author Commented:
Thanks for replying.

What does the SSIS calling the parent stored procedure and capturing the error look like?
0
All Courses

From novice to tech pro — start learning today.