Custom logging in SSIS

Integration Services includes logging features that write log entries when run-time events occur but in cases when you want to capture thing which you can’t do with SSIS Logging for eg ( how many records inserted, updated, and deleted; when it was done; what was the source; who ran the package; what machine did it run on, etc). In that case you can do your own custom logging

Below I will demonstrate with a simple example that how to enable custom logging in SSIS.

First Create Below tables in your database

--Source Table

CREATE TABLE [dbo].[Claim](

       [ClaimSID] [int] NOT NULL,

       [ClaimName] varchar(100)

       )

Insert into Claim values (1,'A'),(2,'B'),(3,'C'),(4,'D')

-- Create similar destination table with below code

select top 0 * into ##claimtest from Claim

--Log Table Code

create table ##Test( packagename varchar(100), startdate datetime, Errordescription varchar(8000),enddate datetime ,taskname varchar(100))

Now Open SSIS and create a basis package with one dataflow and one execute task as below Screenshot and name the package as Eventhandler.

Now in Data flow connect the above source table Claim and connect it to destination and map destination table (##Claimtest)

Next in sql Execute task add the below query that will generates an error, so that we can capture the same in our log table which is ##Test

update ##claimtest

set cliamsid=10

where claimsid=-1

IF you will notice that the actual column name is claimsid and in the update statement I am using cliamSID which is incorrect as there is a difference in spelling, so this will throw an error which we will capture in our custom log table(##Test) along with the package name and other fields.

To enable logging on error event go to event handler window and in event handler drop down select on error. This will captures error messages of the packages.

Drop an sql execute task on that place

And add the below code

insert into ##Test values(?,?,?,Null,?)

Click on Parameter mapping and do a mapping as in below screenshot.

Next again in event handler drop down  select on Postexecute. This will captures the post execute event like when package started and completed.

Drop an sql execute task on that place

insert into ##Test values(?,?,Null,getdate(),?)

Click on Parameter mapping and do a mapping as in below screenshot.

So the logic is if there is an error in that case end date will be blank and in case of no error the enddate will be there with no values in error columns.

Now Run the package and it will track all the details in log tables which is ##Test in our case.

Select * from ##Test