title: SSIS 2012 Error Output from Ole DB Command
body: <p>I have a SSIS Package that calls an OLE DB Command to Update/Insert/Delete records from the data flow.</p>
  
  <p>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.</p>
  
  <p>So I updated the SP that is being called to have an <code>OUTPUT</code> parameter called <code>@ErrorMessage NVARCHAR(4000) OUTPUT</code>.  I set the value of <code>@ErrorMessage</code> to <code>ERROR_MESSAGE</code> in the catch block of the SP. I have followed the instructions from numerous sites (on being <a href="http://www.rad.pasfu.com/index.php?/archives/24-Output-Parameter-of-Stored-Procedure-In-OLE-DB-Command-SSIS.html" rel="nofollow noreferrer">Output Parameter of Stored Procedure In OLE DB Command - SSIS</a>) 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 <em><code>NULL</code></em>.</p>
  
  <p>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?</p>
  
  <p>Sample SP Code (this is just a sample I can't give actual code Catch Block is a match though):</p>
  
  <pre><code>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
  </code></pre>