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.

Solution 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.

Solution 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.

Solution 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

Comments

[Greg]

Wierd, in my sysssislog table, source refers to tasks within the package. I can't find package name anywhere in the table.

[Greg]

I'm not sure if your or Sansom's answers will work. There's nothing coming from SSIS into sp_dts_addlogentry that tells me which package is running so I wouldn't be able to send log information from different packages to different tables. Or am I mistaken?

[William Salzman]

The proc name in 2008 is SP_SSIS_ADDLOGENTRY. The value of the SourceID column where event = 'Package Start' is the GUID of the Package.

[Admin]

FYI: For some reason SSC lost that link or took the blog post down. I've tried searching SSC for it but can't come across it.

[William Salzman]

@ShawnMelton it was removed. It was a good article by Michael Coles. It may be published in one of his books by now.

[William Salzman]

Package name is the source column in the row where event = 'PackageStart' Having gathered that information you can look to the sourceId column to get the guid of the package and executionid to find all of the rows that were from the same execution of that package.

[TT.]

There is no "The above", your answer can appear anywhere in the list of answers depending on how the answers are sorted.