none
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

    Wednesday, March 14, 2012 2:32 AM

Answers

All replies

  • 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!

    Wednesday, March 14, 2012 2:39 AM
  • 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

    Wednesday, March 14, 2012 2:45 AM
  • 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

    Wednesday, March 14, 2012 3:27 AM
  • 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

    Wednesday, March 14, 2012 7:47 AM
  • 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

    Wednesday, March 14, 2012 9:34 AM
  • 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
    Friday, March 16, 2012 2:03 AM
  • 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
    • Marked as answer by Eileen Zhao Tuesday, March 27, 2012 1:32 PM
    Tuesday, March 20, 2012 2:54 AM
  • I am Executing my Stored proc Using Exec Sql Task and i have used try...Catch with Throw Statement. Is there any possibility to log those error using Event Handlers.

    Thanks. 

    Thursday, July 25, 2013 1:16 PM