Stored proc errors in SSIS

irb56
irb56 used Ask the Experts™
on
Hi,

I have a stored procedure that performs a bunch of table inserts and updates in a transaction. The basic structure of the stored proc is given below. I am executing this stored proc from an Execute SQL task in a SSIS package. I am puzzled why the package pauses on the Execute SQL step, keeping it yellow indefinitely. When I run SQL Server Profiler I can see that the stored proc has been called and completed. I copy the command from Profiler into SSMS and execute, which reveals an error - one of the table inserts failed due to a constraint violation. Clearly the CATCH block has been triggered and the transaction rolled back. Why does SSIS not know that the stored proc has failed? If I run sp_who2 this tells me that the SPID for the package connection is sleeping, awaiting a command. Do I need to modify the error handling of my stored proc or is there something I need to do in SSIS to ensure any error with the stored proc triggers a package failure?

CREATE PROC MyProc
AS
BEGIN
      BEGIN TRY
      BEGIN TRAN
      …<some updates, inserts, deletes>
      COMMIT TRAN
      END TRY
      BEGIN CATCH
      ROLLBACK TRAN
      DECLARE @Error_Message VARCHAR(1000);
      SELECT @Error_Message = 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))
                              + ', Error Message: ' + CAST(ERROR_MESSAGE() AS VARCHAR(800))
                              + ', Stored Proc: ' + CAST(ERROR_PROCEDURE() AS VARCHAR(100));
      RAISERROR(@Error_Message,11,1);
      END CATCH
END
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer

Commented:
tell us what exactly you did in execute sql task?
your connection type?
your sql statement?
and any other properties you set

Author

Commented:
The actual stored proc is over 200 lines long and I would rather not copy and paste the real contents here, partly because the amount of code in the proc will obscure the question I'm asking and partly because I don't want to reveal too much of the real database schema. That said, I think the following should provide enough information.

The Execute SQL task is based on an OLE DB connection to a local SQL Server instance, to a database I'll refer to as Imports. The SQLSourceType is Direct Input and the SQLStatement is:
EXEC dbo.ImportAttributes;

See bottom for definition of the ImportAttributes stored proc, noting that it does cross database activity, inserting records in one database and updating records in another. The FailPackageOnFailure property of the Execute SQL task is set to True. CheckpointUsage for the package is currently set to Never.

If any error occurs in the ImportAttributes stored proc I expect this to be trapped in the CATCH block and an error returned with a severity of 11. When such an error is trapped the Execute SQL task turns yellow and the package doesn't complete. When I run EXEC dbo.ImportAttributes in SSMS I see an error message about an attempt to insert a NULL value in some column (let's say the [Description] column of the [Attribute] table).

I wonder if this is because SSIS is interpreting the severity level of the error as too low. However, that doesn't really explain the task hang. I would have thought it would go green.

What I want to ensure is that the Execute SQL task goes red and the package fails should anything cause the ImportAttributes stored proc to enter its CATCH block. I'm not concerned with the reason for the error in the actual stored proc (which I have already resolved).

Definition of stored proc:
CREATE PROC dbo.ImportAttributes
AS
BEGIN
      BEGIN TRY
      BEGIN TRAN
     
        INSERT INTO [Prod].dbo.[User](UserName,ImportID)
        SELECT UserName,ImportID
        FROM dbo.ImportRecords;
        
        UPDATE dbo.ImportRecords
        SET UserID = u.UserID
        FROM [Prod].dbo.[User] u
        WHERE dbo.ImportRecords.ImportID = u.ImportID;
        
        INSERT INTO [Prod].dbo.[Attribute]([Description],ImportID)
        SELECT AttributeDescription,ImportID
        FROM dbo.ImportRecords;
        
        UPDATE dbo.ImportRecords
        SET AttributeID = a.AttributeID
        FROM [Prod].dbo.[Attribute] a
        WHERE dbo.ImportRecords.ImportID = a.ImportID;
        
      COMMIT TRAN
      END TRY
      BEGIN CATCH
      ROLLBACK TRAN
      DECLARE @Error_Message VARCHAR(1000);
      SELECT @Error_Message = 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))
                              + ', Error Message: ' + CAST(ERROR_MESSAGE() AS VARCHAR(800))
                              + ', Stored Proc: ' + CAST(ERROR_PROCEDURE() AS VARCHAR(100));
      RAISERROR(@Error_Message,11,1);
      END CATCH
END
Commented:
Sorry, I have described the problem completely incorrectly. The issue actually relates to a single SQL INSERT statement that is built up dynamically via a Script task and stored in a string variable. The Execute SQL task uses a SQLSourceType of Variable. I'm sorry to have wasted your time. Let me review this issue again and raise it as another question if I think there is still a worthwhile question to ask.
Reza RadConsultant, Trainer

Commented:
Your always welcome,
I hope your problem be solved,
but if problem exists still, just tell me scenario , what did you do? and results?

Best Regards,
Reza
Reza RadConsultant, Trainer

Commented:
@mark,
yes it is OK, set irb56's post as answer. I am agree with you :)

Regards,
Reza

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial