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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
paultran00Author Commented:
Thanks for replying.

What does the SSIS calling the parent stored procedure and capturing the error look like?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.