Calling Stored Procedure using SSIS but not throwing any error

Question

I have stored procedure say 'sp_xyz'. I am calling and running this stored procedure using sql task in SSIS. I put merge statement in this stored procedure. When I run this it is showing its running fine and control is in green color but not getting data. When I run merge statment seperately then it is showing foreign key constraint.

How do I handle error control in stored procedure in SSIS? Please help me

Answer 1

Check the Execution Results, or logs, after running the Task

Also check that the MaximumErrorCount on the Task and package is 0

---

Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!

Answer 2

You stated that when you run the MERGE statement separately that it fails.  Do you mean you ran stored procedure sp_xyz and the procedure failed or did you just execute the MERGE statement?

BTW, don’t prefix your stored procedures with sp_ http://sqlserverpedia.com/blog/sql-server-bloggers/stored-procedure-performance-using-%E2%80%9Csp_%E2%80%9D-prefix-%E2%80%93-myth-or-fact/

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 3

Use a try catch statement and rasie error explicitly from the SP in order for the error to be captured via the script task

something like, note that you have to raise error with high criticality like 16 for it to be caught by SQL task on SSIS

Try

--Your Code

End try

Catch

BEGIN

--Your Code

 RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );

END

---

Abhinav

Answer 4

Use a try catch statement and rasie error explicitly from the SP in order for the error to be captured via the script task

something like, note that you have to raise error with high criticality like 16 for it to be caught by SQL task on SSIS

Try

--Your Code

End try

Catch

BEGIN

--Your Code

 RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );

END

---

Abhinav

If the stroed procedure is not throwing an error, a try catch statement will not work.

---

When you see answers and helpful posts, please click Vote As Helpful, Propose As Answer, and/or Mark As Answer

Jeff Wharton
MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCSD, MCITP, MCDBA
Blog: Mr. Wharty's Ramblings
Twitter: @Mr_Wharty
MC ID: Microsoft Transcript

Answer 5

KIDNUK,

AS Jeff said the error code wouldnt work if SP is not throwing error though i the primary problem was that the SSIS cant capture the error, whereas when you  ran it seperatley via SSMS it failed for Foreign key cosntraint,

 

kdinuk can you please confrim what is the problem you are faced with SSIS not capturing the error as thrown from SP or something else some screenprints must assist.

---

Abhinav

Answer 6

When I run 'sp_xyz' on SSMS its throwing an error 'Foreign key constraint...'. But when I run the same stored procedure keeping that in sql task control in SSIS its not throwing any error. Infact, it should send an email if this fails

Answer 7

Hi kdinuk,

I suggest you can use RAISERROR statement in your stored procedure, and then the SQL Task could catch the error and then failed also. Please refer to Matthew's reply in the similar thread, please see:
http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/1be144c7-4396-4e5b-9e9c-aea7c3ee45a7 

For more information about using RAISERROR, please refer to:
http://msdn.microsoft.com/en-us/library/ms177497.aspx 

Thanks,
Eileen