Hi Guys,
Sometimes definitions are wacky. I thought of writing this article on default logging where I could modify it with my application requirements. And guess what… “Customization with default logging” title rendered in mind.

Just for fun if you think about it, when you customize the default logging; it would be custom logging. But custom is also that where you define everything from scratch. So ultimately it makes 2 types of custom logging: one – by using somebody’s readymade logging work and extend it. And second – by developing it from the scratch.

Today I would show you, how you can tweak (1st type of custom logging :)) the “Default Logging Framework” of SSIS and turn into “Custom Logging“.

Well, it’s really simple.
First you set the “Default Logging” – just configure the “Logging” on control flow tab in SSIS and done. What SSIS does for you internally is following list:

1. Creates a table in your database called “dbo.sysssislog”. Following is its schema.

Note: As this is out-of-box configuration, it’s not advised to be modified. Because if you modify the table (which you totally can) and when you re-enable the default logging in ssis package, it will refresh all the components it has created. Table is part of its list

2. Create a stored proc named “dbo.sp_ssis_addlogentry”. The above Note applies to stored proc as well.

Now, you can simply run your SSIS package and check this table (dbo.sysssislog) is filled-up with log entries. So far this is default logging set-up in your SSIS.

Customization:
Let’s customize a little bit. Let me take hypothetical scenario: I have 2 SSIS packages (named Package1 and Package2) loaded with lots of components. I want to use default logging for both packages but log entries should be in their respective log tables. I mean, SSIS Package 1’s log entries should go to Package1_LogEntries table and SSIS Package2’s log entries should go to Package2_LogEntries table.

1. Create following 2 tables with same definition as “dbo.sysssislog” table. (So, there will be 2 tables with same columns)

• Package1_LogEntries
• Package2_LogEntries

2. Edit the stored proc as shown below to redirect the logging flow to appropriate tables.
[sql]
— Your database name
USE
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE
[dbo].[sp_ssis_addlogentry]
@event sysname,
@computer nvarchar(128),
@operator nvarchar(128),
@source nvarchar(1024),
@sourceid uniqueidentifier,
@executionid uniqueidentifier,
@starttime datetime,
@endtime datetime,
@datacode int,
@databytes image,
@message nvarchar(2048)
AS

— check if information is coming from Package1;
— if yes then insert into Package1_LogEntries table
If (@source = ‘Package1’)
Begin
INSERT INTO dbo.Package1_LogEntries
(event,computer,operator,source,sourceid,executionid,
starttime,endtime,datacode,databytes,message)
VALUES (@event,@computer,@operator,@source,@sourceid,
@executionid,@starttime,@endtime,@datacode,@databytes,@message)
End

— check if information is coming from Package1;
— if yes then insert into Package2_LogEntries table
Else If(@source = ‘Package2’)
Begin
INSERT INTO dbo.Package2_LogEntries
(event,computer,operator,source,sourceid,executionid,
starttime,endtime,datacode,databytes,message)
VALUES (@event,@computer,@operator,@source,@sourceid,
@executionid,@starttime,@endtime,@datacode,@databytes,@message)
End

— for anything apart from Package1 and Package2;
— insert into standard/default logging table
Else
Begin
INSERT INTO dbo.sysssislog
(event,computer,operator,source,sourceid,executionid,
starttime,endtime,datacode,databytes,message)
VALUES (@event,@computer,@operator,@source,@sourceid,
@executionid,@starttime,@endtime,@datacode,@databytes,@message)
End

RETURN 0
[/sql]

That’s it. Run your packages and verify the logging But don’t forget the ground-rules that “If you enable/disable logging in SSIS package then it will refresh the stored proc.” So better not to disable the default logging after implementing this customization.

One more direction:
As you might have noticed that, in stored proc the flow of logging is based on the source which is “Package Name”. So in order to implement this customization on whole solution (containing many SSIS Packages), you can write some code which automatically does following:

• Finds out the number of packages & their names.
• Creates separate tables for each package based on its name.
• alter the stored proc.

And enabling/disabling the default logging on each SSIS: that you have to do manually.

I hope this helps you somewhere.
Thanks, Khilit