Error Handling using SSIS Event Handlers

Create new table for Error log maintenance.

CREATE TABLE [dbo].[ErrorLog](
    [ErrorDescprition] [nvarchar](400) NOT NULL,
    [ErrorCode] [nchar](10) NULL,
    [SourceTable] [nchar](10) NULL,
    [Target] [nchar](10) NULL,
    Date Datetime2
) ON [PRIMARY]




Sample Data flow task

Row Count is used to find how many rows coming from source.

If we want to know the reason when data flow task fails, We can use event handlers
Click on EventHandlers tab
Select related data flow task from Executable s.
Next Select Event Handler as ON ERROR.
Drag Execute SQL Task for writing insert query.

ex:
insert into dbo.ErrorLog ([ErrorDescprition],[ErrorCode],[SourceTable],[Target]) values(?,?,'Test','FlatFile')
Map System Variables to parameters  as shown below.

When error occurs to the given DFT, Execute SQL Task automatically inserts row in the Errorlog Table.