SSIS Register in a table other than SYSSSISLOG.

Question

SSIS seems to insist on registering with the SYSSSISLOG system table. Is there a way to get it to use another table?

I want each packet to register in a different table.

Answer 1

The quick answer is the same as John Sansom's answer. When logging is used, it creates a table and a stored procedure (the name depends on the version from 2005 to 2008). The stored procedure can be modified to do whatever you want. If the stored procedure is deleted, the Sql server re-creates it, but if the stored process exists, the Sql server assumes that it is in order and leaves it alone. This allows you to modify the stored proc to write to any table / s that you want.

Answer 2

Well, you can query this table of huge logs 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 each package in a view, now you know how to do it.

Answer 3

SQL Server Central, , , SQL Server.

This article details how to implement a custom logging provider that redirects SSIS log output to another table. Using this implementation as your structure, you can expand it to meet your requirements.

SSIS Custom Logging the Easy Way