Error handling into a table

Developers need to call the uspErrorHandling stored procedure in their code with the optional parameter list.

ALTER PROCEDURE [dbo].[uspErrorLogTest] (@date as datetime)




AS
  --sample exec [uspErrorLogTest] '11/02/2018'
  
BEGIN TRY

declare @paramList varchar(4000) = '@date=' + cast( @date as varchar)

-- SELECT 1/0

select cast(@date as int) /0

END TRY

BEGIN CATCH

begin

EXEC dbo.uspErrorHandling @paramList




 end

END CATCH


if object_id('uspErrorHandling','P') is not null

drop procedure uspErrorHandling




GO
  
CREATE procedure dbo.uspErrorHandling (@ErrorProcParameterList varchar(4000) = null)

AS




-- Declaration statements
  DECLARE @ErrorNumber int

DECLARE @ErrorMessage varchar(4000)

DECLARE @ErrorSeverity int

DECLARE @ErrorState int

DECLARE @ErrorProcedure varchar(200)

DECLARE @ErrorLine int

DECLARE @UserName varchar(200)

DECLARE @HostName varchar(200)

DECLARE @TransactionDateTime datetime




-- Initialize variables
  SELECT @ErrorNumber = isnull(error_number(),0),

@ErrorMessage = isnull(error_message(),'NULL Message'),

@ErrorSeverity = isnull(error_severity(),0),

@ErrorState = isnull(error_state(),1),

@ErrorLine = isnull(error_line(), 0),

@ErrorProcedure = isnull(error_procedure(),''),

@UserName = SUSER_SNAME(),

@HostName = HOST_NAME(),

@TransactionDateTime = GETDATE();

select @ErrorMessage




-- Insert into the dbo.ErrorLog table
  INSERT INTO dbo.ErrorLog (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorLine,

ErrorProcedure, ErrorParameterList,UserName, HostName,TransactionDateTime)

SELECT @ErrorNumber, @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorLine,

@ErrorProcedure, @ErrorProcParameterList,@UserName, @HostName, @TransactionDateTime


GO


--Table where we log the errors

CREATE TABLE [dbo].[ErrorHandling](

[ErrorHandlingID] [int] IDENTITY(1,1) NOT NULL,

[Error_Number] [int] NOT NULL,

[Error_Message] [varchar](4000) NULL,

[Error_Severity] [smallint] NOT NULL,

[Error_State] [smallint] NOT NULL,

[Error_Procedure] [varchar](200) NOT NULL,

[Error_Line] [int] NOT NULL,

[UserName] [varchar](128) NULL,

[HostName] [varchar](128) NULL,

[TransactionDateTime] [datetime] NOT NULL,

[CreatedDateTime] [datetime] NOT NULL,

PRIMARY KEY CLUSTERED




(
   [ErrorHandlingID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]




GO   

SSIS: Split the file into error file upon error while inserting to the destinaltion table

The SSIS imports a flat file to the SQL table and upon error it writes out the error lines ( including the contents to the a new file e.g. file1_error.txt and inserts the error records ( account number and the row number in this case) to the error ruble in the database. This multiple tasks are possible by the Multicast step.

The script component: Script to update the rowcount is used to count the rows in the source file.




int _RowNumber = 2;

public override void Input0_ProcessInputRow(Input0Buffer Row)



{
 



 



Row.RowNumber = _RowNumber;

_RowNumber += 1;

}