asked on

Stored proc errors in SSIS

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

Reza Rad

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

irb56

ASKER

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

ASKER CERTIFIED SOLUTION

irb56

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Reza Rad

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 Rad

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

Regards,
Reza