SSIS 2012 Error Output from Ole DB Command

Question

I have a SSIS Package that calls an OLE DB Command to Update/Insert/Delete records from the data flow.

Now the problem is occasionally the command may error, due to duplicate Key errors, missing required columns or whatever, and I want to log these errors because the SSIS errors are cryptic and don't tell me what the underlying issue was.

So I updated the SP that is being called to have an OUTPUT parameter called @ErrorMessage NVARCHAR(4000) OUTPUT. I set the value of @ErrorMessage to ERROR_MESSAGE in the catch block of the SP. I have followed the instructions from numerous sites (on being Output Parameter of Stored Procedure In OLE DB Command - SSIS) on using an Output Parameter in a Data Flow, but the new Column I created (SaveError defined as Unicode string Length 4000). However when the SP errors the SaveError column's value never changes from NULL.

Does the Output Parameter not get read in if the underlying SQL ends up throwing an exception? Does anyone have any good ideas on how to accomplish this?

Sample SP Code (this is just a sample I can't give actual code Catch Block is a match though):

CREATE PROCEDURE dbo.UpdateRecord(
     @NewValue1 INT,
     @IdValue INT,
     @ErrorMessage NVARCHAR(4000) OUTPUT
) 
AS
BEGIN
    BEGIN TRY
       MERGE TableA as Tgt
       USING (
             VALUES(@IdValue, @NewValue1)
       ) AS src(IdValue, MyName)
        ON Tgt.Id = src.IdValue
      WHEN NOT MATCHED THEN
         INSERT (Id, MyName)
         VALUES(Src.IdValue, Src.MyName)
      WHEN MATCHED THEN
          UPDATE 
          SET MyName = Src.MyName;

    END TRY
    BEGIN CATCH
       SET @ErrorMessage = ERROR_MESSAGE()

      declare @ErrorSeverity int, @ErrorState int;
      select @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

      raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);

    END CATCH
END

Without seeing the SQL, we can't even begin to help you debug this. Can you also post the error message you get? Also, by your new Column "SaveError Unicode string Length 4000" do you mean a column called SaveError with a datatype nvarchar(4000)? — Thom A
– Thom A ♦, Commented Jan 22, 2018 at 15:07
"Does anyone have any good ideas on how to accomplish this?" Deploy to SSISDB and use the internal logging tools. — Jacob H
– Jacob H, Commented Jan 22, 2018 at 15:08
Updated with Sample SP code, and improved readability I hope.. — MBulava
– MBulava, Commented Jan 22, 2018 at 15:27

Thom A · Accepted Answer · 2018-01-22 15:43:35Z

0

There is no OUTPUT parameter in your SP. (no longer applicable, OP has updated their post)

If, however, your TRY...CATCH is working correctly, then yes, the value of the OUTPUT parameter will be returned. As a simple example:

CREATE PROC chartoint (@Char char(1), @Error nvarchar(4000) OUTPUT)
AS
    BEGIN TRY
        SELECT CONVERT(int, @Char) AS intValue;
    END TRY
    BEGIN CATCH
        SET @Error = ERROR_MESSAGE()

        DECLARE @ErrorSeverity int, @ErrorState int;
        SELECT @Error = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

        RAISERROR (@Error, @ErrorSeverity, @ErrorState);
    END CATCH

GO

DECLARE @ErrorOut nvarchar(4000);

EXEC chartoint 'A', @ErrorOut OUTPUT;
PRINT @ErrorOut;
GO

DROP PROC chartoint;
GO

Are you calling the SP correctly. A a guess, you're not using the OUTPUT clause in your call to the SP.

answered Jan 22, 2018 at 15:43

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MBulava Over a year ago

Yes I know the ErrorMessage is filled from SP (sorry I initally forgot to put OUTPUT in the sample) when I run from SQL, however with SSIS the Output parameter isn't flowing through in the Error Output Flow..

Erdogan Ozdemir · Accepted Answer · 2018-04-11 20:46:19Z

0

I had the same issue in a similar project. I found a workaround but I don't know if it is strong or not. I read that the OLE DB Command should not receive any data from a database. Try this in the CATCH. Add the first line. I was able to have the error in my package.

SELECT ERROR_MESSAGE() as Error__;

SET @Error = ERROR_MESSAGE();

edited Apr 11, 2018 at 20:46

answered Apr 11, 2018 at 12:26

Erdogan Ozdemir

11 bronze badge

Collectives™ on Stack Overflow

SSIS 2012 Error Output from Ole DB Command

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related