SSIS - Log to table other than SYSSSISLOG

Question

SSIS seems to insist on logging to the system table SYSSSISLOG. Is there a way to make it use a different table?

I want each package to log to a different table.

Answer 1

Quick answer is the same as John Sansom's answer: When logging is used, it creates a table and a stored proc (name varies with version between 2005 and 2008) The stored proc can be modified to do whatever you want. If the stored proc is removed Sql server re-creates it, but if the stored proc is there, Sql server assumes it is OK and leaves it alone. This allows you to modify the stored proc to write to whatever table/tables you want.

Answer 2

Well, you can query that huge-ass log table with something like this:

--first, we identify the packages
;with DetectedPackages as (
select source, s.executionid
from dbo.sysssislog as s
where event = 'PackageStart'
group by source, s.executionid
)
--then we use those executionids to display results
select * from dbo.sysssislog as s
join DetectedPackages dp on s.executionid = dp.executionid
where dp.source = 'PackageName'

And if you want to encapsulate every package in a view, now you know how to do that.

Answer 3

Take a look at the following article over on SQL Server Central, you may need to register but it's free to do so and you will find the site to be excellent SQL Server resource.

The article details how to implement a custom Log Provider that redirects the SSIS log output to another table. Using this implementation as your framework you could extend it to meet your requirements.

SSIS Custom Logging the Easy Way