Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Calling Stored Procedure using SSIS but not throwing any error

Answered Calling Stored Procedure using SSIS but not throwing any error

  • Wednesday, March 14, 2012 2:32 AM
     
     
    Sign In to Vote
    0

    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

     

All Replies

  • Wednesday, March 14, 2012 2:39 AM
     
     
    Sign In to Vote
    0

    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:45 AM
     
     
    Sign In to Vote
    1

    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 3:27 AM
     
     
    Sign In to Vote
    0

    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 7:47 AM
     
     
    Sign In to Vote
    0

    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 9:34 AM
     
     
    Sign In to Vote
    0

    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

     
  • Friday, March 16, 2012 2:03 AM
     
     
    Sign In to Vote
    0
    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
     
  • Tuesday, March 20, 2012 2:54 AM
    Moderator
     
     Answered
    Sign In to Vote
    0
    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