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