Search This Blog

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



4 comments:


  1. Very soon this site will be famous among all blogging and site-building users, due to it's nice posts sign in hotmail

    ReplyDelete
  2. Our tools automatically calculate these taxes - you can see them beneath the "Cash Needed" drop down menu. canada mortgage calculator I would recommend Reni to the of my family and friends. mortgage payment calculator canada

    ReplyDelete
  3. The questions is asked, "Why is writing important?" The answer, truthfully, is that no one is exempt from writing. No matter how you hate to write, it is mandatory that some point in your life, you have to do some type of writing; whether it is writing a letter, writing a report, writing a resume and cover letter or just ordinary letter writing, you are succumbed to the laborious task of thinking or letting your thoughts flow from your mind. Writing is obviously inevitable in our lives. https://shahta.org/242177-pochemu-studentam-vazhno-razvivat-navyki-mezhlichnostnogo-obshheniya.html

    ReplyDelete
  4. Thank you so much for providing information about the best and most useful aspect of SSIS.

    SSIS Upsert

    ReplyDelete